SQL Server Data Dictionary Query - Data Table

 1
 2SELECT @@Servername AS ServerName
 3     , DB_NAME() AS DatabaseName
 4     , DB_ID() AS DatabaseId
 5     , sc.name AS SchemaName
 6     , tab.schema_id AS SchemaId
 7     , tab.NAME AS TableName
 8     , tab.object_id AS TableId
 9     , col.NAME AS ColumnName
10     , col.column_id
11     , t.NAME AS DataTypeName
12     , CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN col.max_length/2 ELSE col.max_length END AS Length_Size
13     , t.max_length
14     , CAST(col.is_identity AS INT) AS is_identity
15     , col.scale
16     , t.precision AS Precision
17     , t.NAME + CASE WHEN t.is_user_defined = 0 THEN ISNULL('(' + CASE WHEN t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE col.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(col.max_length / 2 AS VARCHAR(4)) ELSE CAST(col.max_length AS VARCHAR(4)) END END WHEN t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(col.scale AS VARCHAR(4)) WHEN t.NAME IN('decimal', 'numeric') THEN CAST(col.precision AS VARCHAR(4)) + ', ' + CAST(col.scale AS VARCHAR(4)) END + ')', '') ELSE ':' +
18	 (
19	 SELECT c_t.NAME + ISNULL('(' + CASE WHEN c_t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE c.max_length 
20	 WHEN -1 THEN 'MAX' ELSE 
21	      CASE WHEN t.NAME IN('nchar', 'nvarchar') 
22		  THEN CAST(c.max_length / 2 AS VARCHAR(4)) 
23		  ELSE CAST(c.max_length AS VARCHAR(4)) 
24		  END 
25	 END 
26	 WHEN c_t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(c.scale AS VARCHAR(4)) 
27	 WHEN c_t.NAME IN('decimal', 'numeric') THEN CAST(c.precision AS VARCHAR(4)) + ', ' + CAST(c.scale AS VARCHAR(4)) END + ')', '')
28	 FROM sys.columns AS c
29	 INNER JOIN sys.types AS c_t ON c.system_type_id = c_t.user_type_id
30	 WHERE
31	 c.object_id = col.object_id
32	 AND c.column_id = col.column_id
33	 AND c.user_type_id = col.user_type_id
34	 ) END AS [DataType]
35     , CASE WHEN col.collation_name IS NOT NULL THEN 1 ELSE 0 END has_collation_name
36     , col.collation_name
37     , col.is_nullable
38     , CASE WHEN col.is_nullable = 0 THEN 'N' ELSE 'Y' END AS nullable
39     , CASE WHEN def.DEFINITION IS NOT NULL THEN 1 ELSE 0 END AS has_default_value
40     , CASE WHEN def.DEFINITION IS NOT NULL THEN def.DEFINITION ELSE '' END AS default_value
41     , CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_primary_key
42     , CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS primary_key
43     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS is_foreign_key
44     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS foreign_key
45     , CASE WHEN uk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_unique_key
46     , CASE WHEN uk.column_id IS NOT NULL THEN 'UK' ELSE '' END AS unique_key
47     , CASE WHEN ch.check_const IS NOT NULL THEN 1 ELSE 0 END AS is_check_contraint
48     , CASE WHEN ch.check_const IS NOT NULL THEN ch.check_const ELSE '' END AS check_contraint
49     , col.is_computed
50     , cc.DEFINITION AS computed_column_definition
51     , ep.value AS comments
52FROM sys.computed_columns AS cc
53RIGHT OUTER JOIN sys.tables AS tab
54INNER JOIN sys.schemas AS sc ON tab.schema_id = sc.schema_id
55LEFT OUTER JOIN sys.columns AS col ON tab.object_id = col.object_id
56LEFT OUTER JOIN sys.types AS t ON col.user_type_id = t.user_type_id
57LEFT OUTER JOIN sys.default_constraints AS def ON def.object_id = col.default_object_id
58LEFT OUTER JOIN (SELECT sys.index_columns.object_id
59     , sys.index_columns.column_id
60FROM sys.index_columns
61INNER JOIN sys.indexes ON sys.index_columns.object_id = sys.indexes.object_id
62AND sys.index_columns.index_id = sys.indexes.index_id
63WHERE
64     (sys.indexes.is_primary_key = 1)) AS pk ON col.object_id = pk.object_id
65AND col.column_id = pk.column_id
66LEFT OUTER JOIN (SELECT fc.parent_column_id
67     , fc.parent_object_id
68FROM sys.foreign_keys AS f
69INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
70GROUP BY fc.parent_column_id , fc.parent_object_id) AS fk ON fk.parent_object_id = col.object_id
71AND fk.parent_column_id = col.column_id
72LEFT OUTER JOIN (
73SELECT parent_column_id
74     , parent_object_id
75     , 'Check' AS check_const
76FROM sys.check_constraints AS c
77GROUP BY parent_column_id , parent_object_id
78) AS ch ON col.column_id = ch.parent_column_id
79AND col.object_id = ch.parent_object_id
80LEFT OUTER JOIN (
81SELECT index_columns_1.object_id
82     , index_columns_1.column_id
83FROM sys.index_columns AS index_columns_1
84INNER JOIN sys.indexes AS indexes_1 ON indexes_1.index_id = index_columns_1.index_id
85AND indexes_1.object_id = index_columns_1.object_id
86WHERE
87     (indexes_1.is_unique_constraint = 1)
88GROUP BY index_columns_1.object_id , index_columns_1.column_id
89) AS uk ON col.column_id = uk.column_id
90AND col.object_id = uk.object_id
91LEFT OUTER JOIN sys.extended_properties AS ep ON tab.object_id = ep.major_id
92AND col.column_id = ep.minor_id
93AND ep.name = 'MS_Description'
94AND ep.class_desc = 'OBJECT_OR_COLUMN' ON cc.object_id = tab.object_id
95AND cc.column_id = col.column_id