MSSQL2000, 2005 테이블 명세서 출력퀘리

--//SQL Database documentation script
--//Description: T-SQL script to generate the database document for SQL server 2000/2005
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
 If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
   set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server  2000')
   set @SqlVersion = '2000'
else 
   set @SqlVersion = '2005'

Print ''
Print ''
Print ''
Print '
                

                
                











                
                '
Print ''
set nocount on
   if @SqlVersion = '2000' 
      begin
      insert into #Tables (Object_id, Name, Type, [description])
         --FOR 2000
         select object_id(table_name),  '[' + table_schema + '].[' + table_name + ']',  
         case when table_type = 'BASE TABLE'  then 'Table'   else 'View' end,
         cast(p.value as varchar(4000))
         from information_schema.tables t
         left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description' 
         order by table_type, table_schema, table_name
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Tables (Object_id, Name, Type, [description])
      --FOR 2005
      Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 
            case when type = 'V' then 'View' when type = 'U' then 'Table' end,  
            cast(p.value as varchar(4000))
            from sys.objects o 
               left outer join sys.schemas s on s.schema_id = o.schema_id 
               left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' 
            where type in ('U', 'V') 
            order by type, s.name, o.name
      end
Set @maxi = @@rowcount
set @i = 1
print '
Index
'
print '' 
While(@i <= @maxi)
begin
   select @Output =  '' 
         from #Tables where id = @i
   
   print @Output
   set @i = @i + 1
end
print '
Sr
Object
Type
' + Cast((@i) as varchar) + '
' + name + '
' + Type + '
'
set @i = 1
While(@i <= @maxi)
begin
   --table header
   select @Output =  '' + Type + ':' + name + '',  @description = [description]
         from #Tables where id = @i
   
   print '


'
   print @Output
   print '
Index
'
   print '
Description
' + isnull(@description, '') + '
'
   --table columns
   truncate table #Columns 
   if @SqlVersion = '2000' 
      begin
      insert into #Columns  (Name, Type, Nullable, [description])
      --FOR 2000
      Select c.name, 
               type_name(xtype) + (
               case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
                  then '(' + cast(length as varchar) + ')' 
                when type_name(xtype) = 'decimal'  
                     then '(' + cast(prec as varchar) + ',' + cast(scale as varchar)   + ')' 
               else ''
               end            
               ), 
               case when isnullable = 1 then 'Y' else 'N'  end, 
               cast(p.value as varchar(8000))
            from syscolumns c
               inner join #Tables t on t.object_id = c.id
               left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description' 
            where t.id = @i
            order by c.colorder
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Columns  (Name, Type, Nullable, [description])
      --FOR 2005   
      Select c.name, 
               type_name(user_type_id) + (
               case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
                  then '(' + cast(max_length as varchar) + ')' 
                when type_name(user_type_id) = 'decimal'  
                     then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar)   + ')' 
               else ''
               end            
               ), 
               case when is_nullable = 1 then 'Y' else 'N'  end,
               cast(p.value as varchar(4000))
      from sys.columns c
            inner join #Tables t on t.object_id = c.object_id
            left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
      where t.id = @i
      order by c.column_id
      end
   Set @maxj =   @@rowcount
   set @j = 1
   print '
Table Columns
' 
   print '' 
   
   While(@j <= @maxj)
   begin
      select @Output = '' 
         from #Columns  where id = @j
      
      print    @Output    
      Set @j = @j + 1;
   end
   print '
Sr.
Name
Datatype
Nullable
Description
' + Cast((@j) as varchar) + '
' + isnull(name,'')  + '
' +  upper(isnull(Type,'')) + '
' + isnull(Nullable,'N') + '
' + isnull([description],'') + '
'
   --reference key
   truncate table #FK
   if @SqlVersion = '2000' 
      begin
      insert into #FK  (Name, col, refObj, refCol)
   --      FOR 2000
      select object_name(constid), s.name,  object_name(rkeyid) ,  s1.name  
            from sysforeignkeys f
               inner join sysobjects o on o.id = f.constid
               inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
               inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
               inner join #Tables t on t.object_id = f.fkeyid
            where t.id = @i
            order by 1
      end   
   else if @SqlVersion = '2005' 
      begin
      insert into #FK  (Name, col, refObj, refCol)
--      FOR 2005
      select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)     
      from sys.foreign_keys f
         inner  join  sys.foreign_key_columns  fc  on f.object_id = fc.constraint_object_id   
         inner join #Tables t on t.object_id = f.parent_object_id
      where t.id = @i
      order by f.name
      end
   
   Set @maxj =   @@rowcount
   set @j = 1
   if (@maxj >0)
   begin
      print '
Refrence Keys
' 
      print ''
      While(@j <= @maxj)
      begin
         select @Output = '' 
            from #FK  where id = @j
         print @Output
         Set @j = @j + 1;
      end
      print '
Sr.
Name
Column
Reference To
' + Cast((@j) as varchar) + '
' + isnull(name,'')  + '
' +  isnull(col,'') + '
[' + isnull(refObj,'N') + '].[' +  isnull(refCol,'N') + ']
'
   end
   --Default Constraints 
   truncate table #Constraint
   if @SqlVersion = '2000' 
      begin
      insert into #Constraint  (Name, col, definition)
      select object_name(c.constid), col_name(c.id, c.colid), s.text
            from sysconstraints c
               inner join #Tables t on t.object_id = c.id
               left outer join syscomments s on s.id = c.constid
            where t.id = @i 
            and 
            convert(varchar,+ (c.status & 1)/1)
            + convert(varchar,(c.status & 2)/2)
            + convert(varchar,(c.status & 4)/4)
            + convert(varchar,(c.status & 8)/8)
            + convert(varchar,(c.status & 16)/16)
            + convert(varchar,(c.status & 32)/32)
            + convert(varchar,(c.status & 64)/64)
            + convert(varchar,(c.status & 128)/128) = '10101000'
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Constraint  (Name, col, definition)
      select c.name,  col_name(parent_object_id, parent_column_id), c.definition 
      from sys.default_constraints c
         inner join #Tables t on t.object_id = c.parent_object_id
      where t.id = @i
      order by c.name
      end
   Set @maxj =   @@rowcount
   set @j = 1
   if (@maxj >0)
   begin
      print '
Default Constraints
' 
      print ''
      While(@j <= @maxj)
      begin
         select @Output = '' 
            from #Constraint  where id = @j
         print @Output
         Set @j = @j + 1;
      end
   print '
Sr.
Name
Column
Value
' + Cast((@j) as varchar) + '
' + isnull(name,'')  + '
' +  isnull(col,'') + '
' +  isnull(definition,'') + '
'
   end

   --Check  Constraints
   truncate table #Constraint
   if @SqlVersion = '2000' 
      begin
      insert into #Constraint  (Name, col, definition)
         select object_name(c.constid), col_name(c.id, c.colid), s.text
            from sysconstraints c
               inner join #Tables t on t.object_id = c.id
               left outer join syscomments s on s.id = c.constid
            where t.id = @i 
            and ( convert(varchar,+ (c.status & 1)/1)
               + convert(varchar,(c.status & 2)/2)
               + convert(varchar,(c.status & 4)/4)
               + convert(varchar,(c.status & 8)/8)
               + convert(varchar,(c.status & 16)/16)
               + convert(varchar,(c.status & 32)/32)
               + convert(varchar,(c.status & 64)/64)
               + convert(varchar,(c.status & 128)/128) = '00101000' 
            or convert(varchar,+ (c.status & 1)/1)
               + convert(varchar,(c.status & 2)/2)
               + convert(varchar,(c.status & 4)/4)
               + convert(varchar,(c.status & 8)/8)
               + convert(varchar,(c.status & 16)/16)
               + convert(varchar,(c.status & 32)/32)
               + convert(varchar,(c.status & 64)/64)
               + convert(varchar,(c.status & 128)/128) = '00100100')
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Constraint  (Name, col, definition)
         select c.name,  col_name(parent_object_id, parent_column_id), definition 
         from sys.check_constraints c
            inner join #Tables t on t.object_id = c.parent_object_id
         where t.id = @i
         order by c.name
      end
   Set @maxj =   @@rowcount
   
   set @j = 1
   if (@maxj >0)
   begin
      print '
Check  Constraints
' 
      print ''
      While(@j <= @maxj)
      begin
         select @Output = '' 
            from #Constraint  where id = @j
         print @Output 
         Set @j = @j + 1;
      end
      print '
Sr.
Name
Column
Definition
' + Cast((@j) as varchar) + '
' + isnull(name,'')  + '
' +  isnull(col,'') + '
' +  isnull(definition,'') + '
'
   end

   --Triggers 
   truncate table #Constraint
   if @SqlVersion = '2000' 
      begin
      insert into #Constraint  (Name)
         select tr.name
         FROM sysobjects tr
            inner join #Tables t on t.object_id = tr.parent_obj
         where t.id = @i and tr.type = 'TR'
         order by tr.name
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Constraint  (Name)
         SELECT tr.name
         FROM sys.triggers tr
            inner join #Tables t on t.object_id = tr.parent_id
         where t.id = @i
         order by tr.name
      end
   Set @maxj =   @@rowcount
   
   set @j = 1
   if (@maxj >0)
   begin
      print '
Triggers
' 
      print ''
      While(@j <= @maxj)
      begin
         select @Output = '' 
            from #Constraint  where id = @j
         print @Output 
         Set @j = @j + 1;
      end
      print '
Sr.
Name
Description
' + Cast((@j) as varchar) + '
' + isnull(name,'')  + '

'
   end
   --Indexes 
   truncate table #Indexes
   if @SqlVersion = '2000' 
      begin
      insert into #Indexes  (Name, type, cols)
         select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name 
         from sysindexes i
            inner join sysindexkeys k  on k.indid = i.indid  and k.id = i.id
            inner join syscolumns c on c.id = k.id and c.colorder = k.colid
            inner join #Tables t on t.object_id = i.id
         where t.id = @i and i.name not like '_WA%'
         order by i.name, i.keycnt
      end
   else if @SqlVersion = '2005' 
      begin
      insert into #Indexes  (Name, type, cols)
         select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end,  col_name(i.object_id, c.column_id)
            from sys.indexes i 
               inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
               inner join #Tables t on t.object_id = i.object_id
            where t.id = @i
            order by i.name, c.column_id
      end
   Set @maxj =   @@rowcount
   
   set @j = 1
   set @sr = 1
   if (@maxj >0)
   begin
      print '
Indexes
' 
      print '' 
      set @Output = ''
      set @last = ''
      set @current = ''
      While(@j <= @maxj)
      begin
         select @current = isnull(name,'') from #Indexes  where id = @j
                
         if @last <> @current  and @last <> ''
            begin   
            print '' 
            set @Output  = ''
            set @sr = @sr + 1
            end
         
            
         select @Output = @Output + cols + '
' , @typ = type
               from #Indexes  where id = @j
         
         set @last = @current    
         Set @j = @j + 1;
      end
      if @Output <> ''
            begin   
            print '' 
            end
      print '
Sr.
Name
Type
Columns
' + Cast((@sr) as varchar) + '
' + @last + '
' + @typ + '
' + @Output  + '
' + Cast((@sr) as varchar) + '
' + @last + '
' + @typ + '
' + @Output  + '
'
   end
    Set @i = @i + 1;
   --Print @Output 
end

Print '
                





                
                




                '
Print ''
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes 
set nocount off