Data dictionary script

--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