Another Script to Generate a DataDictionary

,

Based on Ramesh Kondaparthy's script (Script to Generate DataDictionary for Database, posted 8/5/2005), I made a few format changes and included a few more columns.

I modified Ramesh's script so that I could copy the result and paste it into Word as the beginning of a Data Dictionary document.

(Hint: Once you've pasted the result into Word, do a Select All then an Insert Table.)

Columns included are:
table_name
column_order
column_name
column_datatype
column_length
column_precision
column_scale
column_allownull
column_default
column_description

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER PROCEDURE Generate_getDataDictionary     
AS    

DECLARE @table_name nvarchar(128)    

CREATE table #tblDataDictionary 
	(table_name [sql_variant], 
	 column_order [sql_variant], 
	 column_name [sql_variant],
	 column_datatype [sql_variant],
	 column_length [sql_variant],
	 column_precision [sql_variant],
	 column_scale [sql_variant],
	 column_allownull [sql_variant],
	 column_default [sql_variant],
	 column_description [sql_variant])

DECLARE tablenames_cursor CURSOR FOR     
	SELECT name FROM sysobjects where type = 'U' and status > 1 order by name    
	OPEN tablenames_cursor    
	FETCH NEXT FROM tablenames_cursor INTO @table_name    
	WHILE @@FETCH_STATUS = 0    
		BEGIN    
		--CODE FOR THE COLUMNS  
		INSERT INTO #tblDataDictionary   
			SELECT
				obj.[name] AS 'table_name',    
				col.colorder AS 'column_order',
				col.[name] AS 'column_name',
				typ.[name] AS 'column_datatype',
				col.[length] AS 'column_length',
				CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[prec] AS nvarchar(255)) ELSE '' END AS 'column_precision',
				CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[scale] AS nvarchar(255)) ELSE '' END AS 'column_scale',
				convert(varchar(254), rtrim(substring('   YES',(ColumnProperty (col.id, col.name,'AllowsNull')*3)+1,3))), 
				ISNULL(com.text,'') AS 'column_default',
				ISNULL(ext.value,'') AS 'column_description'     
			FROM sysobjects obj
				INNER join syscolumns col on obj.id = col.id     
				INNER JOIN systypes typ ON col.xtype = typ.xtype 
                                LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, N'column', null) ext on col.name = ext.objname    
				LEFT OUTER JOIN syscomments com ON col.cdefault = com.id
			WHERE obj.name = @table_name
				AND typ.[name] <> 'sysname'    
			ORDER BY col.colorder    
		--CODE ENDS HERE   
		FETCH NEXT FROM tablenames_cursor INTO @table_name    
	END    
 
CLOSE tablenames_cursor    
DEALLOCATE tablenames_cursor   

SELECT * FROM #tblDataDictionary ORDER BY table_name,Column_Order  



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

5 (1)

Share

Share

Rate

5 (1)