DataDictionary Generator Query in SQL

Go

go
if exists (select * from sysobjects where name = 'DataDictionary')
    DROP table DataDictionary

Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataDictionary](
    [TblName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Name] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Type] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Length] [smallint] NULL,
    [Precision] [smallint] NULL,
    [Scale] [int] NULL,
    [Allow Nulls] [bit] NULL
) ON [PRIMARY]



Go

-------------------------------------
go
if exists (select * from sysobjects where name = 'Data_Dictionary')
    DROP procedure Data_Dictionary

Go

create Procedure Data_Dictionary
(
@TableName varchar(50)
)

As
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].['+@tableName+']') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)


Begin

    insert into datadictionary(TblName) select @tableName

    insert into datadictionary
    SELECT  '',
    cols.name as 'Name',
    typs.name as 'Type',
    cols.Length,
    cols.prec as 'Precision',
    cols.Scale,
    Allownulls as 'Allow Nulls' 
    FROM syscolumns cols
    INNER JOIN systypes typs ON cols.xusertype=typs.xusertype
    WHERE id = OBJECT_ID(@tableName)

    insert into datadictionary(TblName) select ''

End



GO


-------------------------------------

Declare @tbname as varchar(100)

Declare cur4 cursor for
select name from sysobjects where  xtype  = 'U'

Begin
open cur4

fetch next from cur4 into  @tbname
if @@FETCH_STATUS = 0
Begin
        while @@FETCH_STATUS = 0
        begin       
               
                Exec Data_Dictionary @tbname
                fetch next from cur4 into  @tbname
               
        End
end
else
Begin       
        close cur4
        deallocate cur4
End
close cur4
deallocate cur4
End

Go

select * from DataDictionary

Go