sql:sql_server_data_dictionary

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
sql:sql_server_data_dictionary [2022/05/02 15:59] – Raphaelsql:sql_server_data_dictionary [2022/05/02 16:09] (current) – Raphael
Line 78: Line 78:
 \\ \\
  
-===== Columns =====+===== Columns (for tables) =====
 \\  \\ 
  
Line 84: Line 84:
 SELECT  SELECT 
  t.object_id as table_object_id  t.object_id as table_object_id
- ,s.name as schema_id+ ,s.name as schema_name
  ,t.name as table_name  ,t.name as table_name
  ,c.name as column_name  ,c.name as column_name
Line 171: Line 171:
  ON t.object_id = cc.object_id  ON t.object_id = cc.object_id
  AND c.column_id = cc.column_id  AND c.column_id = cc.column_id
 +
 +order by s.name, t.name, c.column_id;
 +</code>
 +\\
 +
 +===== Columns (for views) =====
 +\\ 
 +
 +<code>
 +SELECT 
 +
 + t.object_id as view_object_id
 + ,s.name as schema_name
 + ,t.name as view_name
 + ,c.name as column_name
 + ,c.column_id
 + ,ty.name as data_type
 + ,ty.name + COALESCE('(' +
 + CASE WHEN ty.name IN('binary', 'char', 'varchar', 'varbinary') THEN 
 + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length) END
 + WHEN ty.name IN('nchar', 'nvarchar') THEN 
 + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length/2) END
 + WHEN ty.name IN('decimal', 'numeric') THEN CONVERT(nvarchar(10), c.precision) + ',' + CONVERT(nvarchar(10), c.scale)
 + WHEN ty.name in ('datetime2', 'datetimeoffset', 'time') then CONVERT(nvarchar(10),c.scale )
 + ELSE NULL
 + END + ')',
 + '') as data_type_ext
 + ,case when c.is_nullable = 0 then 'N' 
 + else 'Y' end as nullable
 + ,ep.comments
 +
 +
 +FROM sys.views t
 +
 +JOIN sys.schemas s 
 + ON t.schema_id = s.schema_id
 +
 +JOIN sys.columns c 
 + ON t.object_id = c.object_id
 +
 +JOIN sys.types ty 
 + ON c.user_type_id = ty.user_type_id
 +
 +LEFT JOIN (
 + SELECT major_id, minor_id, CONVERT(nvarchar(max), value) as comments
 + FROM sys.extended_properties
 + WHERE name = 'MS_Description'
 + AND class_desc = 'OBJECT_OR_COLUMN'
 +) as ep 
 + ON t.object_id = ep.major_id
 + AND c.column_id = ep.minor_id
  
 order by s.name, t.name, c.column_id; order by s.name, t.name, c.column_id;
 </code> </code>