生成数据字典(sqlserver)

发布时间：2020-12-12 14:55:49 所属栏目：MsSql教程来源：网络整理

导读：?--//SQL Database documentation script?? --//Author: Nitin Patel,Email: nitinpatel31@gmail.com?? --//Date:18-Feb-2008?? --//Description: T-SQL script to generate the database document for SQL server 2000/2005?? Declare @i Int,@maxi Int?? D

?--//SQL Database documentation script??
--//Author: Nitin Patel,Email: nitinpatel31@gmail.com?? --//Date:18-Feb-2008?? --//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,Type Varchar(155),Nullable varchar(2),[description] varchar(4000))?? create Table #Fk(id int identity(1,col Varchar(155),refObj varchar(155),refCol varchar(155))?? create Table #Constraint(id int identity(1,definition varchar(1000))?? create Table #Indexes(id int identity(1,Type Varchar(25),cols varchar(1000))?? ?? ?If (substring(@@VERSION,1,25 ) = 'Microsoft SQL Server 2005')?? ??? set @SqlVersion = '2005'?? else if (substring(@@VERSION,26 ) = 'Microsoft SQL Server? 2000')?? ??? set @SqlVersion = '2000'?? else??? ??? set @SqlVersion = '2005'?? ?? ?? Print '<head>'?? Print '<title>::' + DB_name() + '::</title>'?? Print '<style>'?? ?????? Print '???? body {'?? Print '???? font-family:verdana;'?? Print '???? font-size:9pt;'?? Print '???? }'?? ?????????? Print '???? td {'?? Print '???? font-family:verdana;'?? Print '???? font-size:9pt;'?? Print '???? }'?? ?????????? Print '???? th {'?? Print '???? font-family:verdana;'?? Print '???? font-size:9pt;'?? Print '???? background:#d3d3d3;'?? Print '???? }'?? Print '???? table'?? Print '???? {'?? Print '???? background:#d3d3d3;'?? Print '???? }'?? Print '???? tr'?? Print '???? {'?? Print '???? background:#ffffff;'?? Print '???? }'?? Print ' </style>'?? Print '</head>'?? Print '<body>'?? ?? 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,[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 '<table border="0" cellspacing="0" cellpadding="0" width="550px" align="center"><tr><td colspan="3" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table>'?? print '<table border="0" cellspacing="1" cellpadding="0" width="550px" align="center"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>'??? While(@i <= @maxi)?? begin?? ??? select @Output =? '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>'??? ??????????? from #Tables where id = @i?? ?????? ??? print @Output?? ??? set @i = @i + 1?? end?? print '</table><br />'?? ?? set @i = 1?? While(@i <= @maxi)?? begin?? ??? --table header?? ??? select @Output =? '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>',? @description = [description]?? ??????????? from #Tables where id = @i?? ?????? ??? print '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td align="right"><a href="#index">Index</a></td></tr>'?? ??? print @Output?? ??? print '</table><br />'?? ??? print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Description</b></td></tr><tr><td>' + isnull(@description,'') + '</td></tr></table><br />'??? ?? ??? --table columns?? ??? truncate table #Columns??? ??? if @SqlVersion = '2000'??? ??????? begin?? ??????? insert into #Columns? (Name,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,[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) + ',??? ??????????????????? 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 border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Table Columns</b></td></tr></table>'??? ??? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th><th>Nullable</th><th>Description</th></tr>'??? ?????? ??? While(@j <= @maxj)?? ??? begin?? ??????? select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'')? + '</td><td width="150px">' +? upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Nullable,'N') + '</td><td>' + isnull([description],'') + '</td></tr>'??? ??????????? from #Columns? where id = @j?? ?????????? ??????? print?? @Output??????? ??????? Set @j = @j + 1;?? ??? end?? ?? ??? print '</table><br />'?? ?? ??? --reference key?? ??? truncate table #FK?? ??? if @SqlVersion = '2000'??? ??????? begin?? ??????? insert into #FK? (Name,col,refObj,refCol)?? ??? --????? FOR 2000?? ??????? select object_name(constid),? 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,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 '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Refrence Keys</b></td></tr></table>'??? ??????? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Reference To</th></tr>'??? ?? ??????? While(@j <= @maxj)?? ??????? begin?? ?? ??????????? select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'')? + '</td><td width="150px">' +? isnull(col,'') + '</td><td>[' + isnull(refObj,'N') + '].[' +? isnull(refCol,'N') + ']</td></tr>'??? ??????????????? from #FK? where id = @j?? ?? ??????????? print @Output?? ??????????? Set @j = @j + 1;?? ??????? end?? ?? ??????? print '</table><br />'?? ??? end?? ?? ??? --Default Constraints??? ??? truncate table #Constraint?? ??? if @SqlVersion = '2000'??? ??????? begin?? ??????? insert into #Constraint? (Name,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,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 '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Default Constraints</b></td></tr></table>'??? ??????? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Value</th></tr>'??? ?? ??????? While(@j <= @maxj)?? ??????? begin?? ?? ??????????? select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td><td>' +? isnull(definition,'') + '</td></tr>'??? ??????????????? from #Constraint? where id = @j?? ?? ??????????? print @Output?? ??????????? Set @j = @j + 1;?? ??????? end?? ?? ??? print '</table><br />'?? ??? end?? ?? ?? ??? --Check? Constraints?? ??? truncate table #Constraint?? ??? if @SqlVersion = '2000'??? ??????? begin?? ??????? insert into #Constraint? (Name,definition)?? ??????????? select object_name(c.constid),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 & 128)/128) = '00100100')?? ?? ??????? end?? ??? else if @SqlVersion = '2005'??? ??????? begin?? ??????? insert into #Constraint? (Name,definition)?? ??????????? select c.name,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 '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Check? Constraints</b></td></tr></table>'??? ??????? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Column</th><th>Definition</th></tr>'??? ?? ??????? While(@j <= @maxj)?? ??????? begin?? ?? ??????????? select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="250px">' + isnull(name,'') + '</td></tr>'??? ??????????????? from #Constraint? where id = @j?? ??????????? print @Output??? ??????????? Set @j = @j + 1;?? ??????? end?? ?? ??????? print '</table><br />'?? ??? 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 '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Triggers</b></td></tr></table>'??? ??????? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Description</th></tr>'??? ?? ??????? While(@j <= @maxj)?? ??????? begin?? ??????????? select @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'')? + '</td><td></td></tr>'??? ??????????????? from #Constraint? where id = @j?? ??????????? print @Output??? ??????????? Set @j = @j + 1;?? ??????? end?? ?? ??????? print '</table><br />'?? ??? 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,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 '<table border="0" cellspacing="0" cellpadding="0" width="750px"><tr><td><b>Indexes</b></td></tr></table>'??? ??????? print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><tr><th>Sr.</th><th>Name</th><th>Type</th><th>Columns</th></tr>'??? ??????? 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 '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output? + '</td></tr>'??? ??????????????? set @Output? = ''?? ??????????????? set @sr = @sr + 1?? ??????????????? end?? ?????????????? ?????????????????? ??????????? select @Output = @Output + cols + '<br />',@typ = type?? ??????????????????? from #Indexes? where id = @j?? ?????????????? ??????????? set @last = @current?????? ??????????? Set @j = @j + 1;?? ??????? end?? ??????? if @Output <> ''?? ??????????????? begin????? ??????????????? print '<tr><td width="20px" align="center">' + Cast((@sr) as varchar) + '</td><td width="150px">' + @last + '</td><td width="150px">' + @typ + '</td><td>' + @Output? + '</td></tr>'??? ??????????????? end?? ?? ??????? print '</table><br />'?? ??? end?? ?? ??? Set @i = @i + 1;?? ??? --Print @Output??? end?? ?? ?? Print '</body>'?? Print '</html>'?? ?? drop table #Tables?? drop table #Columns?? drop table #FK?? drop table #Constraint?? drop table #Indexes??? set nocount off?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!