Script to Generate DataDictionary for Database

,

This Procedure is used to generate the datadistionary for a database.

It gives following columns :

table_id,
table_name,
column_order,
column_name,
column_description

Usage:
Create the Procedure in the database where the datadisctionary need to be generated.
Call : Execute Generate_getDataDictionary

Return the result in Tabular format.

CREATE procedure Generate_getDataDictionary     
As    
            DECLARE @table_name nvarchar(128)    
	    Create table #tblDataDictionary 
			(table_id [sql_variant] ,
			 table_name [sql_variant], 
			 column_order [sql_variant], 
			 column_name [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
                                              o.[id] as 'table_id',     
                                              o.[name] as 'table_name',    
                                              c.colorder as 'column_order',    
                                              c.[name] as 'column_name',    
                                              e.value as 'column_description'     
                                        FROM sysobjects o 
					INNER join syscolumns c on o.id = c.id     
                                        LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',    
                                                              N'user',N'dbo',N'table', @table_name, N'column', null) e on c.name = e.objname    
					WHERE o.name = @table_name    
					ORDER BY c.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

Rate

5 (1)

Share

Share

Rate

5 (1)