title: How to get structural details of database
body: <p>I need structural details of all user defined database in below format:-</p>
  
  <p>name type details</p>
  
  <p>In which details include- if it is a table than Column A(type,Size),ColumnB(Type,Size)....,ConstraintA,ConstarintB...,IndexeA(Indexed Columns),IndexB(IndexedColumns).....</p>
  
  <p>If it is a function or procedure that Body of that function or procedure.</p>
  
  <p>Below is the query which I have created but it is taking around 8 mins for 10 databases:-</p>
  
  <pre><code>CREATE TABLE #modelStructure (Name VARCHAR(500),TYPE VARCHAR(256),DETAILS NVARCHAR(MAX))
  DECLARE @dbTableName NVARCHAR(500)='#modelStructure'
  USE MODEL
  SELECT * INTO #AllTablesAndViews FROM sys.objects ob WHERE ob.is_ms_shipped=0 AND ob.type IN ('U','V')
  SELECT tab.name AS TableName,tab.object_id,tab.type_desc AS Type,col.name AS ColumnName,typ.name AS ColumnType,typ.max_length AS ColumnSize
                  ,ob.name AS ConstraintName,trig.name AS TriggerName INTO #tempTableDetails
  FROM #AllTablesAndViews tab 
  LEFT JOIN sys.columns col ON tab.object_id=col.object_id
  LEFT JOIN sys.objects ob ON ob.parent_object_id=tab.object_id
  LEFT JOIN sys.triggers trig ON trig.parent_id=tab.object_id
  LEFT JOIN sys.types typ ON col.user_type_id=typ.user_type_id
  WHERE tab.name &lt;&gt;'sysdiagrams' AND ob.type_desc LIKE '%CONSTRAINT' AND tab.is_ms_shipped=0 
  GROUP BY tab.name ,tab.object_id,tab.type_desc ,col.name,typ.name,typ.max_length,ob.name ,trig.name
  SELECT * INTO #stuffedTable FROM
  (
  SELECT ttd.TableName AS Name,ttd.Type, 
  'COLUMNS :-'+
  STUFF((SELECT ',' + t1.ColumnName+'('+'Type:'+CONVERT(VARCHAR(200),t1.ColumnType)+' Size:'+CONVERT(VARCHAR(200),t1.ColumnSize)+')'
          FROM #tempTableDetails t1 
          WHERE t1.object_id=ttd.object_id 
          GROUP BY t1.TableName,t1.ColumnName,t1.ColumnType,t1.ColumnSize
          ORDER BY t1.ColumnName
          FOR XML PATH('')),1,1,'')+' || '+
  'CONSTRAINTS:-'+ STUFF((SELECT ',' + t1.ConstraintName 
          FROM #tempTableDetails t1 
          WHERE t1.object_id=ttd.object_id 
          GROUP BY t1.TableName,t1.ConstraintName
          ORDER BY t1.ConstraintName
          FOR XML PATH('')),1,1,'')+' || '+   
  'TRIGGERS:-'+ STUFF((SELECT ',' + ISNULL(t1.TriggerName,'') 
          FROM #tempTableDetails t1 
          WHERE t1.object_id=ttd.object_id 
          GROUP BY t1.TableName,t1.TriggerName
          ORDER BY t1.TriggerName
          FOR XML PATH('')),1,1,'')+' || ' AS Details 
  
  FROM #tempTableDetails ttd 
  )t
  GROUP BY t.Details,t.Name,t.Type ORDER BY Name
  
  ;WITH cteIndexDetails
  AS
  (
  
  SELECT 
        ind.name IndexName ,
         t.name TableName,
         col.name IndexColumn
  FROM 
       sys.indexes ind 
  INNER JOIN 
       sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
  INNER JOIN 
       sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
  INNER JOIN 
       sys.tables t ON ind.object_id = t.object_id 
  WHERE 
  
        t.is_ms_shipped = 0 
       AND t.name &lt;&gt; 'sysdiagrams'
       AND ic.is_included_column=0 GROUP BY ind.name,t.name,col.name
  )
  SELECT  id.TableName,'INDEXES :-'+STUFF((SELECT ',' + t1.IndexName +'( Index Column: '+t1.IndexColumn+' )'
          FROM cteIndexDetails t1 
          WHERE t1.TableName=id.TableName 
          GROUP BY t1.TableName,t1.IndexName,t1.IndexColumn
          ORDER BY t1.IndexName
          FOR XML PATH('')),1,1,'')AS [IndexDetails] INTO #indexDetails
          FROM cteIndexDetails id GROUP BY TableName
  
  INSERT INTO #modelStructure 
  SELECT Name,TYPE,DETAILS 
  FROM
  (
  SELECT st.Name,st.Type,(st.Details+''+id.IndexDetails) AS Details FROM #stuffedTable st INNER JOIN #indexDetails id ON st.Name=id.TableName 
  
  
  UNION 
  
  SELECT
         r.ROUTINE_NAME       AS 'Name'
        ,r.ROUTINE_TYPE COLLATE database_default AS 'Type'
        ,sm.definition AS 'DETAILS'
  FROM  INFORMATION_SCHEMA.ROUTINES r 
  INNER JOIN sys.sql_modules sm ON sm.object_id=object_id(r.ROUTINE_NAME)
  WHERE (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure')
  )t ORDER BY t.TYPE,t.Name
  
  DROP TABLE #AllTablesAndViews
  DROP TABLE #tempTableDetails
  DROP TABLE #stuffedTable
  DROP TABLE #indexDetails
  
  
  DROP TABLE #modelStructure
  </code></pre>