--//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 nvarchar(4000) Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description nvarchar(4000) create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] nvarchar(4000)) create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] nvarchar(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)) Print '' Print '' Print '::' + DB_name() + '::' Print '' Print '' Print '' set nocount on insert into #Tables (Object_id, Name, Type, [description]) Select o.object_id, '[' + s.name + '].[' + o.name + ']', case when type = 'V' then 'View' when type = 'U' then 'Table' end, cast(p.value as nvarchar(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') --특정테이블 조회조건 --and o.name like '%RETI%' --and o.name not like '%_2012%' order by type, s.name, o.name Set @maxi = @@rowcount set @i = 1 print '' print '
Index
' print '' print '' print '' While(@i <= @maxi) begin select @Output = '' from #Tables where id = @i print @Output set @i = @i + 1 end print '
SrObjectDescription
' + Cast((@i) as varchar) + '' + name + '' + isnull([description], '') + '

' 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 insert into #Columns (Name, Type, Nullable, [description]) Select c.name, type_name(user_type_id) + (case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) ='char') then '(' + cast(max_length as varchar) + ')' when (type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='nchar') then '(' + cast(max_length/2 as nvarchar) + ')' 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 nvarchar(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 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.NameDatatypeNullableDescription
' + Cast((@j) as varchar) + '' + isnull(name,'') + '' + upper(isnull(Type,'')) + '' + isnull(Nullable,'N') + '' + isnull([description],'') + '

' ------------------------------------ reference key ------------------------------------ truncate table #FK insert into #FK (Name, col, refObj, refCol) 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 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.NameColumnReference To
' + Cast((@j) as varchar) + '' + isnull(name,'') + '' + isnull(col,'') + '[' + isnull(refObj,'N') + '].[' + isnull(refCol,'N') + ']

' end ------------------------------------ Default Constraints ------------------------------------ truncate table #Constraint 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 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.NameColumnValue
' + Cast((@j) as varchar) + '' + isnull(name,'') + '' + isnull(col,'') + '' + isnull(definition,'') + '

' end ------------------------------------ Check Constraints ------------------------------------ truncate table #Constraint 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 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.NameColumnDefinition
' + Cast((@j) as varchar) + '' + isnull(name,'') + '' + isnull(col,'') + '' + isnull(definition,'') + '

' end ------------------------------------ Triggers ------------------------------------ truncate table #Constraint 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 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.NameDescription
' + Cast((@j) as varchar) + '' + isnull(name,'') + '

' end ------------------------------------ Indexes ------------------------------------ truncate table #Indexes 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 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.NameTypeColumns
' + Cast((@sr) as varchar) + '' + @last + '' + @typ + '' + @Output + '
' + Cast((@sr) as varchar) + '' + @last + '' + @typ + '' + @Output + '

' end Set @i = @i + 1; end Print '' Print '' drop table #Tables drop table #Columns drop table #FK drop table #Constraint drop table #Indexes set nocount off