Generate Database Design Document

USE [pOrbis1]
GO
/****** Object:  StoredProcedure [dbo].[GenerateDDD]    Script Date: 3/5/2015 4:52:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateDDD] (@tabName as varchar(100)=null)
as
begin

Declare @Table_Name as varchar(200)
Declare @Table_Desc as varchar(2000)


CREATE TABLE [dbo].[#DDD](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [SNo] [varchar](500) NULL,
       [FieldName] [varchar](100) NULL,
       [Key] [varchar](20) NULL,
       [DataType] [varchar](50) NULL,
       [Require] [varchar](50) NULL,
       [DefaultValue] [varchar](50) NULL,
       [ColumnDescription] [sql_variant] NULL,
       [ForeignKeyColumn] [varchar](500) NULL
) ON [PRIMARY]

--exec GenerateDDD 'FileImportLog'
Declare rsTable Cursor For select  TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE'
and table_name = isnull(@tabName,table_name) and TABLE_NAME not in ('abc','sysdiagrams')
order by table_name
OPEN rsTable
FETCH NEXT FROM rsTable INTO @Table_Name
WHILE @@FETCH_STATUS=0
BEGIN
       print @Table_Name
       sELECT        @Table_Desc = isnull(t.name  + '->' + convert(varchar(2000),td.value ),@Table_Name)
       FROM          sysobjects t
       INNER JOIN  sysusers u
              ON            u.uid = t.uid
       LEFT OUTER JOIN sys.extended_properties td
              ON            td.major_id = t.id
              AND    td.minor_id = 0
              AND           td.name = 'MS_Description'
       WHERE t.type = 'u' and t.name=@Table_Name
     
     
       insert into #DDD(SNo,FieldName,[Key],DataType,Require,DefaultValue,ColumnDescription,ForeignKeyColumn)
     

       select convert(varchar(500),@Table_Desc)SNo,''FieldName,''[Key],''DataType,''Require,''DefaultValue,''ColumnDescription,''ForeignKeyColumn
       union all
       select quotename('SNo'),quotename('FieldName'),quotename('Key'),quotename('DataType'),quotename('Require'),quotename('DefaultValue'),quotename('ColumnDescription'),quotename('ForeignKeyColumn')
       union all


       select colInfo.* , replace(isnull(quotename(convert(varchar(5000),coldescription.column_desc),'"'),''),char(13),', ')  as [ColumnDescription],isnull(FKDetails.PK_Table + '-> ' + FKDetails.PK_Column,'') as 'ForeignKeyColumn'
       from
       (SELECT
       Convert(varchar(50),ordinal_position) as SNo
       , i.Column_Name as [FieldName], isnull(GetInfo .[key],'') as 'Key'
       , [DataType] = CASE when Data_Type in ( 'varchar' , 'char' ) then Data_Type + '(' + convert( varchar (10), character_maximum_length) + ')' else Data_Type end
       , CASE when is_nullable= 'NO' then 'Yes' else 'No' end as 'Require'
       , CASE when column_default='(getdate())' then 'getdate()' else replace ( replace ( replace ( isnull (column_default ,'' ),'(' , '' ), ')' ,'' ),'''' , '' ) end as [DefaultValue]
       --, isnull ( GetInfo. Description, '' ) as 'Description'
       FROM information_schema.columns i left join
       ( SELECT b.column_name,
       [key] = CASE a.constraint_type when 'PRIMARY KEY' then 'PK' else 'FK' end
       --,description = replace(replace(replace(replace( b. constraint_name,'FK_',''),'PK_',''),@Table_Name,''),'_','')
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a inner join INFORMATION_SCHEMA. KEY_COLUMN_USAGE b on a .constraint_name = b. constraint_name
       AND a.table_name=b. table_name
       WHERE a.table_name= @Table_Name ) GetInfo
       on i.column_name = getinfo .column_name
       WHERE i .table_name =@Table_Name)colInfo
       left join
       (sELECT       t.name AS [table],
                           td.value AS [table_desc],
                     c.name AS [column],
                     cd.value AS [column_desc]
       FROM          sysobjects t
       INNER JOIN  sysusers u
              ON            u.uid = t.uid
       LEFT OUTER JOIN sys.extended_properties td
              ON            td.major_id = t.id
              AND    td.minor_id = 0
              AND           td.name = 'MS_Description'
       INNER JOIN  syscolumns c
              ON            c.id = t.id
       LEFT OUTER JOIN sys.extended_properties cd
              ON            cd.major_id = c.id
              AND           cd.minor_id = c.colid
              AND           cd.name = 'MS_Description'
       WHERE t.type = 'u' and cd.value is not null and t.name=@Table_Name) coldescription
       on colInfo.[FieldName]=coldescription.[column]
       left join
       (SELECT
              K_Table = FK.TABLE_NAME,
              FK_Column = CU.COLUMN_NAME,
              PK_Table = PK.TABLE_NAME,
              PK_Column = PT.COLUMN_NAME,
              Constraint_Name = C.CONSTRAINT_NAME
       FROM
              INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
              ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
              ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
       INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
              ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
       INNER JOIN (
                           SELECT
                                  i1.TABLE_NAME,
                                  i2.COLUMN_NAME
                           FROM
                                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                                  ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                           WHERE
                                  i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                        ) PT
              ON PT.TABLE_NAME = PK.TABLE_NAME) FKDetails
              on colInfo.[FieldName]=FKDetails.FK_Column and FKDetails.K_Table=@Table_Name
             
       union all    
       select '','','','','','','',''

       FETCH NEXT FROM rsTable INTO @Table_Name
END
CLOSE rsTable
DEALLOCATE rsTable
select * from #DDD




end