bdill

adm_DocumentTables2_old.sql

Mar 5th, 2021 (edited)
617
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-05
  4. -- Script home: https://pastebin.com/xLBJktdQ
  5. -- Other useful files: https://pastebin.com/u/bdill (scripts, data files, etc.)
  6. CREATE OR ALTER PROCEDURE dbo.adm_DocumentTables2
  7.     @TableNameLike VARCHAR(200) = '%%'
  8.   , @ColumnNameLike VARCHAR(200) = '%%'
  9. AS
  10. BEGIN
  11.     IF OBJECT_ID('tempdb..#tmpKeys') IS NOT NULL
  12.         DROP TABLE #tmpKeys
  13.  
  14.     CREATE TABLE #tmpKeys (
  15.       SchemaName VARCHAR(100) NOT NULL
  16.     , TableName VARCHAR(200) NOT NULL
  17.     , ColumnName VARCHAR(200) NOT NULL
  18.     , ConstraintType VARCHAR(100) NOT NULL
  19.     )
  20.     INSERT INTO #tmpKeys(SchemaName, TableName, ColumnName, ConstraintType)
  21.     SELECT CCU.TABLE_SCHEMA, CCU.TABLE_NAME, CCU.COLUMN_NAME, TC.CONSTRAINT_TYPE
  22.     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
  23.     JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
  24.  
  25.     SELECT
  26.           S.name AS SchemaName
  27.         , T.name AS TableName
  28.         , C.name AS ColumnName
  29.         , CASE
  30.           WHEN Y.name IN ('varchar', 'nvarchar') AND C.max_length = -1 THEN UPPER(Y.name) + '(MAX)'
  31.           WHEN Y.name IN ('char', 'varchar', 'datetime2') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.max_length) + ')'
  32.           WHEN Y.name IN ('nvarchar', 'nchar') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.max_length/2) + ')'
  33.           WHEN Y.name IN ('float', 'numeric', 'decimal') THEN UPPER(Y.name) + '(' + CONVERT(VARCHAR(10), C.precision) + ', ' + CONVERT(VARCHAR(10), C.scale) + ')'
  34.           ELSE UPPER(Y.name) END AS DataTypeSpec
  35.         , CASE C.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END AS Nullable
  36.         , CASE C.is_identity WHEN 1 THEN 'IDENTITY' ELSE '' END AS [Identity]
  37.         , CASE WHEN DC.name IS NULL THEN ''
  38.           --ELSE 'CONSTRAINT ' + DC.name + ' DEFAULT ' + SUBSTRING(DC.definition, 2, LEN(DC.definition)-2 ) END AS [Default]
  39.           ELSE SUBSTRING(DC.definition, 2, LEN(DC.definition)-2 ) END AS [Default]
  40.         , CASE WHEN K.ConstraintType = 'PRIMARY KEY' THEN 'PK'
  41.                WHEN K.ConstraintType = 'FOREIGN KEY' THEN 'FK' ELSE '' END AS [Key]
  42.         , ISNULL(refT.name + '.' + refC.name, '') AS RefersTo
  43.         , C.column_id
  44.         --, '--' AS Divider, Y.name AS DataType, C.max_length, C.precision, C.scale, C.system_type_id, C.user_type_id
  45.     FROM sys.tables AS T
  46.     JOIN sys.schemas AS S ON S.schema_id = T.schema_id
  47.     JOIN sys.columns AS C ON C.object_id = T.object_id
  48.     JOIN sys.types AS Y ON Y.user_type_id = C.user_type_id
  49.     LEFT OUTER JOIN sys.default_constraints AS DC ON DC.parent_object_id = T.object_id AND DC.parent_column_id = C.column_id
  50.     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
  51.     LEFT OUTER JOIN sys.tables AS refT ON refT.object_id = FKC.referenced_object_id
  52.     LEFT OUTER JOIN sys.columns AS refC ON refC.object_id = FKC.referenced_object_id AND FKC.referenced_column_id = refC.column_id
  53.     LEFT OUTER JOIN #tmpKeys AS K ON K.SchemaName = S.name AND K.TableName = T.name AND K.ColumnName = C.name
  54.     WHERE T.name <> 'sysdiagrams'
  55.     AND T.name LIKE @TableNameLike
  56.     AND C.name LIKE @ColumnNameLike
  57.     ORDER BY S.name, T.name, C.column_id
  58. END;
RAW Paste Data