Another Script to Generate a DataDictionary

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