DBA

Data dictionary script

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:\ugpresentation\datadictionary.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 AND DC.parent_object_id = C.object_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

	

7 thoughts on “Data dictionary script

  1. Hi, Mala. Thanks for the interesting post. Just a heads up, though…

    The forum software makes a bit of train wreck of your code. Even after fixing the “smart quotes” and “long dashes” that appear in SSMS after a copy’n’paste, it would appear that there is a bit of a problem with the double-quoted column names.

    Like

  2. Ah… finally figured it out. All of the double-quotes in SSMS had to be replace by two single quotes to work as well as the other replacements that were necessary.

    After that got it all working and just as another heads-up, there seems to be a lot of near duplication in the rows for any given column.

    Like

  3. Moving the code to a code window did the trick, Mala. Thanks for that.

    As for the near duplication, I’m not sure it’s avoidable because of the nature of the data and the many to many relationships that occur when joining such meta-data tables.

    Like

    1. Dear Jeff, actually found a bug in the join to default constraints (not exactly a bug but somehow cut and paste left out one join). Fixed it now!! Thanks for letting me know dear sir.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.