--//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 ''
print ''
print 'Sr | Object | Description |
'
While(@i <= @maxi)
begin
select @Output = '' + Cast((@i) as varchar)
+ ' | '
+ name + ' | ' + isnull([description], '') + ' |
'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '
'
set @i = 1
While(@i <= @maxi)
begin
------------------------------------ table header ------------------------------------
select @Output = '' + Type + ':' + name + ' |
', @description = [description]
from #Tables where id = @i
print '
Index |
'
print @Output
print '
'
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 ''
print 'Sr. | Name | Datatype | Nullable | Description |
'
While(@j <= @maxj)
begin
select @Output = '' + Cast((@j) as varchar) + ' | ' + isnull(name,'') + ' | ' + upper(isnull(Type,'')) + ' | ' + isnull(Nullable,'N') + ' | ' + isnull([description],'') + ' |
'
from #Columns where id = @j
print @Output
Set @j = @j + 1;
end
print '
'
------------------------------------ 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 ''
print 'Sr. | Name | Column | Reference To |
'
While(@j <= @maxj)
begin
select @Output = '' + Cast((@j) as varchar) + ' | ' + isnull(name,'') + ' | ' + isnull(col,'') + ' | [' + isnull(refObj,'N') + '].[' + isnull(refCol,'N') + '] |
'
from #FK where id = @j
print @Output
Set @j = @j + 1;
end
print '
'
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 ''
print 'Sr. | Name | Column | Value |
'
While(@j <= @maxj)
begin
select @Output = '' + Cast((@j) as varchar) + ' | ' + isnull(name,'') + ' | ' + isnull(col,'') + ' | ' + isnull(definition,'') + ' |
'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '
'
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 ''
print 'Sr. | Name | Column | Definition |
'
While(@j <= @maxj)
begin
select @Output = '' + Cast((@j) as varchar) + ' | ' + isnull(name,'') + ' | ' + isnull(col,'') + ' | ' + isnull(definition,'') + ' |
'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '
'
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 ''
print 'Sr. | Name | Description |
'
While(@j <= @maxj)
begin
select @Output = '' + Cast((@j) as varchar) + ' | ' + isnull(name,'') + ' | |
'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '
'
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 ''
print 'Sr. | Name | Type | Columns |
'
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 '' + Cast((@sr) as varchar) + ' | ' + @last + ' | ' + @typ + ' | ' + @Output + ' |
'
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 '' + Cast((@sr) as varchar) + ' | ' + @last + ' | ' + @typ + ' | ' + @Output + ' |
'
end
print '
'
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