I restarted speaking with New Stars of Data today – I gave a talk on Database documentation. Below is the script I created to pull metadata into an excel file if you need it. Thank you Mladen Prajdic(t) for being the original inspiration for this.
–Query to pull data dictionary from sql server metadata.
–Author: Mala Mahadevan
–Date 3/11/2021 V1.00
–Read:
–Have xp_cmdshell enabled for export to excel file
–Run on database you need metadata from
–Works on SQL Server 2012+
–Set variable @outputfilename to whatever filename you desire output to go to.
DROP TABLE IF EXISTS ##TempExportData
DECLARE @DBName varchar(100),
@SQLStmt nvarchar(4000),
@ShellStmt varchar(8000),
@OutputFilename varchar(100)
SELECT @OutputFilename = ‘c:ugpresentationdatadictionary.xls’
BEGIN TRY
SELECT @DBName = db_name()
SELECT @SQLStmt = ‘USE ‘ + @DBName + ‘;’+’
SELECT
”Database” AS [Database Name],
”Schema” AS [Schema Name],
”Table Name” AS [Table Name],
”Column Name” AS [Column Name],
”DataType” AS [Data Type],
”Length” AS [Length],
”Precision” AS [Precision],
”Scale” AS [Scale],
”IsNullable” AS [IsNullable],
”IsPrimaryKey” AS [IsPrimaryKey],
”Primary Key Constraint” AS [PK Constraint],
”IsIndexed” AS [IsIndexed],
”IsIncludedIndex” AS [IsIncludedIndex],
”Index Name” AS [Index Name],
”Foreign Key Constraint” AS [FK Constraint],
”Parent Table” AS [Parent Table],
”Default Constraint” AS [Default Constraint],
”Comments” AS [Comments]
INTO ##tempExportData
FROM sys.tables
UNION
SELECT
DB_NAME() AS [Database Name],
OBJECT_SCHEMA_NAME(T.[object_id]) AS [Schema Name],
T.[name] AS [Table Name],
C.[name] AS [Column Name],
UPPER(TY.[name]) AS DataType,
CAST(C.[max_length] AS VARCHAR(10)) AS [Length] ,
CAST(C.[precision] AS VARCHAR(5)) AS [Precision],
CAST(C.[scale] AS VARCHAR(5)) AS [Scale],
IIF(C.[is_nullable] = 0,”N”,”Y”) AS IsNullable,
IIF(ISNULL(I.is_primary_key,0) = 0, ”N”,”Y”) AS IsPrimaryKey,
KC.name as [Primary Key Constraint],
(CASE WHEN IC.index_column_id > 0 THEN ”Y” ELSE ”N” END) AS IsIndexed,
IIF(ISNULL(is_included_column, 0) = 0, ”Y”, ”N”) AS IsIncludedIndex,
I.name AS [Index Name],
OBJECT_NAME(FK.constraint_object_id) as [Foreign Key Constraint],
OBJECT_NAME(FK.referenced_object_id) as [Parent Table],
DC.name AS [Default Constraint],
EP.value AS Comments
FROM sys.tables AS T
INNER JOIN
sys.all_columns C
ON T.[object_id] = C.[object_id]
INNER JOIN
sys.types TY
ON C.[system_type_id] = TY.[system_type_id]
AND C.[user_type_id] = TY.[user_type_id]
LEFT JOIN
sys.index_columns IC
ON IC.object_id = T.object_id
AND C.column_id = IC.column_id
LEFT JOIN
sys.indexes I
ON I.object_id = T.object_id
AND IC.index_id = I.index_id
LEFT JOIN
sys.foreign_key_columns FK
ON FK.parent_object_id = T.object_id AND FK.parent_column_id = C.column_id
LEFT JOIN
sys.key_constraints KC
ON KC.parent_object_id = T.object_id AND IC.index_column_id = KC.unique_index_id
LEFT JOIN
sys.default_constraints DC
ON DC.parent_column_id = C.column_id
LEFT JOIN
sys.extended_properties EP
ON EP.major_id = T.object_id AND EP.minor_id = C.column_id
–ORDER BY T.[name], C.[column_id]
‘
EXECUTE sp_executesql @SQLStmt
--select * from ##tempexportdata
SET @ShellStmt = 'bcp "' + ' SELECT * from ##TempExportData" queryout "' + @OutputFileName + '" -c -T -CRAW'
Exec master..xp_cmdshell @ShellStmt
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH