bdill

adm_DocumentTables2.sql

Mar 16th, 2021 (edited)
921
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Desc: Document tables and columns in a SQL Server database
  2. -- License: ShoutOutWare - give me a shout out on Twitter @bdill if this script helped you. :)
  3. -- Auth: Brian Dill 2021-03-16
  4. -- Script home: https://pastebin.com/ahCCQw3Q
  5. -- Other useful files: https://pastebin.com/u/bdill (scripts, data files, etc.)
  6.  
  7. CREATE OR ALTER PROCEDURE dbo.adm_Document_Tables2
  8.  
  9. -- =======================================================================================
  10. -- Desc:    Selects meta data about tables/columns including the MS_Description extended propertes (if any) in each table/column
  11. -- Date:    2021-03-16  bdill   initial creation (borrowed from the old adm_Document_Tables)
  12. -- Note:    Paste this in cell L2 of a dump to Excel to get SQL code to update the MS_Description extended properties in SQL
  13. --          ="EXEC dbo.adm_TableAndColumnExtendedProperty_ups '"&A2&"', '"&B2&"', '"&C2&"', '"&K2&"'"
  14.  
  15.   @TableNameLike VARCHAR(100) = '%'
  16. , @ColumnNameLike VARCHAR(100) = '%'
  17.  
  18. AS
  19. BEGIN
  20.     SET NOCOUNT ON
  21.  
  22.     -- =======================================================================================
  23.     -- Create a temp table to hold all of the descriptions for alter joining.
  24.  
  25.     IF OBJECT_ID('tempdb..#tmpDescColumns') IS NOT NULL
  26.         DROP TABLE #tmpDescColumns
  27.  
  28.     CREATE TABLE #tmpDescColumns (  
  29.           DescID               INT IDENTITY(1,1) NOT NULL
  30.         , SchemaName           VARCHAR(255) NULL
  31.         , TableName            VARCHAR(100)  NULL
  32.         , ColumnName           VARCHAR(100)  NULL
  33.         , Description          VARCHAR(2000) NULL
  34.     )
  35.  
  36.     -- =======================================================================================
  37.     -- temp table for all PKs FKs
  38.    
  39.     IF OBJECT_ID('tempdb..#tmpKeys') IS NOT NULL
  40.         DROP TABLE #tmpKeys
  41.  
  42.     CREATE TABLE #tmpKeys (
  43.           SchemaName VARCHAR(100) NOT NULL
  44.         , TableName VARCHAR(200) NOT NULL
  45.         , ColumnName VARCHAR(200) NOT NULL
  46.         , ConstraintType VARCHAR(100) NOT NULL
  47.     )
  48.     INSERT INTO #tmpKeys(SchemaName, TableName, ColumnName, ConstraintType)
  49.     SELECT CCU.TABLE_SCHEMA, CCU.TABLE_NAME, CCU.COLUMN_NAME, TC.CONSTRAINT_TYPE
  50.     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
  51.     JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
  52.  
  53.  
  54.     -- =======================================================================================
  55.     -- Get a cursor for all of the tables to get MS_Description for each table and column
  56.  
  57.     DECLARE cur_tables INSENSITIVE CURSOR FOR
  58.             SELECT T.object_id, S.name, T.name
  59.             FROM sys.tables AS T
  60.             JOIN sys.schemas AS S ON S.schema_id = T.schema_id
  61.             WHERE T.name LIKE @TableNameLike
  62.             ORDER BY T.name
  63.  
  64.     DECLARE @id INT, @current_schema_name VARCHAR(255), @current_table_name VARCHAR(256)
  65.     OPEN cur_tables
  66.     FETCH NEXT FROM cur_tables INTO @id, @current_schema_name, @current_table_name
  67.     WHILE @@fetch_status <> -1
  68.         BEGIN
  69.             -- =======================================================================================
  70.             -- Insert the descriptions for the columns in the current table into #tmpDescColumns
  71.  
  72.             INSERT INTO #tmpDescColumns (SchemaName, TableName, ColumnName, Description)
  73.             SELECT    @current_schema_name, @current_table_name, objname, Convert(varchar(2000), value)
  74.             FROM   fn_listextendedproperty ('MS_Description', 'schema', @current_schema_name, 'table', @current_table_name, 'column', NULL)
  75.  
  76.             -- =======================================================================================
  77.             -- Insert the descriptions for the current table into #tmpDescColumns
  78.                    
  79.             INSERT INTO #tmpDescColumns (SchemaName, TableName, ColumnName, Description)
  80.             SELECT    @current_schema_name, @current_table_name, '', Convert(varchar(2000), value)
  81.             FROM   fn_listextendedproperty ('MS_Description', 'schema', @current_schema_name, 'table', @current_table_name, NULL, NULL)
  82.  
  83.             FETCH NEXT FROM cur_tables INTO @id, @current_schema_name, @current_table_name
  84.         END
  85.  
  86.     CLOSE cur_tables
  87.     DEALLOCATE cur_tables
  88.  
  89.     -- =======================================================================================
  90.     -- Final SELECT
  91.  
  92.     SELECT
  93.           S.name AS SchemaName
  94.         , T.name AS TableName
  95.         , C.name AS ColumnName
  96.         , CASE
  97.             WHEN Y.name IN ('varchar', 'nvarchar') AND C.max_length = -1 THEN UPPER(Y.name) + '(MAX)'
  98.             WHEN Y.name IN ('char', 'varchar', 'datetime2') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.max_length) + ')'
  99.             WHEN Y.name IN ('nvarchar', 'nchar') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.max_length/2) + ')'
  100.             WHEN Y.name IN ('float', 'numeric', 'decimal') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.precision) + ', ' + CONVERT(VARCHAR(10), C.scale) + ')'
  101.             ELSE UPPER(Y.name) END AS DataTypeSpec
  102.         , CASE C.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END AS Nullable
  103.         , CASE C.is_identity WHEN 1 THEN 'IDENTITY' ELSE '' END AS [Identity]
  104.         , CASE WHEN DC.name IS NULL THEN ''
  105.             --ELSE 'CONSTRAINT ' + DC.name + ' DEFAULT ' + SUBSTRING(DC.definition, 2, LEN(DC.definition)-2 ) END AS [Default]
  106.             ELSE SUBSTRING(DC.definition, 2, LEN(DC.definition)-2 ) END AS [Default]
  107.         , CASE WHEN K.ConstraintType = 'PRIMARY KEY' THEN 'PK'
  108.                 WHEN K.ConstraintType = 'FOREIGN KEY' THEN 'FK' ELSE '' END AS [Key]
  109.         , ISNULL(refT.name + '.' + refC.name, '') AS RefersTo
  110.         , C.column_id
  111.         , ISNULL(TDC.Description, '') AS Description
  112.         --, '--' AS Divider, Y.name AS DataType, C.max_length, C.precision, C.scale, C.system_type_id, C.user_type_id
  113.     FROM sys.tables AS T
  114.     JOIN sys.schemas AS S ON S.schema_id = T.schema_id
  115.     JOIN sys.columns AS C ON C.object_id = T.object_id
  116.     JOIN sys.types AS Y ON Y.user_type_id = C.user_type_id
  117.     LEFT OUTER JOIN sys.default_constraints AS DC ON DC.parent_object_id = T.object_id AND DC.parent_column_id = C.column_id
  118.     LEFT OUTER JOIN sys.foreign_key_columns AS FKC ON FKC.parent_object_id = C.object_id AND FKC.parent_column_id = C.column_id
  119.     LEFT OUTER JOIN sys.tables AS refT ON refT.object_id = FKC.referenced_object_id
  120.     LEFT OUTER JOIN sys.columns AS refC ON refC.object_id = FKC.referenced_object_id AND FKC.referenced_column_id = refC.column_id
  121.     LEFT OUTER JOIN #tmpKeys AS K ON K.SchemaName = S.name AND K.TableName = T.name AND K.ColumnName = C.name
  122.     LEFT OUTER JOIN #tmpDescColumns AS TDC ON TDC.SchemaName = S.name AND TDC.TableName = T.name AND TDC.ColumnName = c.name
  123.     WHERE T.name <> 'sysdiagrams'
  124.     AND T.name LIKE @TableNameLike
  125.     AND C.name LIKE @ColumnNameLike
  126.            
  127.     UNION
  128.  
  129.     -- Row for each table
  130.     SELECT S.name AS SchemaName
  131.         , T.name AS Tablename
  132.         , '', '', '', '', '', '', '', '' AS column_id, ISNULL(TDC.Description, '') AS Description
  133.     FROM sys.tables AS T
  134.     JOIN sys.schemas AS S ON S.schema_id = T.schema_id
  135.     LEFT OUTER JOIN #tmpDescColumns AS TDC ON TDC.SchemaName = S.name AND TDC.TableName = T.name AND TDC.ColumnName = ''
  136.     WHERE T.name LIKE @TableNameLike
  137.     ORDER BY S.name, T.name, C.column_id
  138.  
  139.     -- =======================================================================================
  140.     -- cleanup
  141.     DROP TABLE #tmpDescColumns
  142.  
  143. END
  144.  
RAW Paste Data