原理很简单：将获取数据字典信息（通过动态视图获取）存入到目标表（数据字典表）中即可。
本人自用实例
1）创建相关的字典表
use YWMonitorGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOif not exists (select * from dbo.sysobjects where id = object_id('Data_dictionary')) begin CREATE TABLE [dbo].[Data_dictionary]( [code] int identity(1,1) primary key , [库名] [varchar](100) NULL, [表名] [varchar](100) NULL, [表说明] [sql_variant] NULL, [字段序号] int NULL, [字段名] [varchar](100) NULL, [标识] [varchar](100) NULL, [主键] [varchar](100) NULL, [类型] [varchar](50) NULL, [占用字节数] int NULL, [长度] int NULL, [小数位数] int NULL, [允许空] [varchar](100) NULL, [默认值] [varchar](100) NULL, [字段说明] [sql_variant] NULL, [更新时间] [datetime] not NULL ) ON [PRIMARY] end else print 'exists'GOSET ANSI_PADDING OFFGO
2）将获取的字典信息录入表中
declare @dbname nvarchar(500),@sqltext nvarchar(2000)declare mycursor cursor forselect name from sys.databases where database_id >4 and name not in ('xxxDB') and state_desc = 'ONLINE'open mycursorfetch next from mycursor into @dbnamewhile @@FETCH_STATUS=0begin set @sqltext= 'use ['+@dbname+']INSERT INTO [YWMonitor].[dbo].[Data_dictionary] ([库名] ,[表名] ,[表说明] ,[字段序号] ,[字段名] ,[标识] ,[主键] ,[类型] ,[占用字节数] ,[长度] ,[小数位数] ,[允许空] ,[默认值] ,[字段说明] ,[更新时间])SELECT 库名 = '''+@dbname+''', 表名= d.name , 表说明=case when a.colorder=1 then isnull(f.value,'''') else '''' end, 字段序号=a.colorder, 字段名=a.name, 标识=case when COLUMNPROPERTY( a.id,a.name,''IsIdentity'')=1 then ''√''else '''' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype=''PK'' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then ''√'' else '''' end, 类型=b.name, 占用字节数=a.length, 长度=COLUMNPROPERTY(a.id,a.name,''PRECISION''), 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,''Scale''),0), 允许空=case when a.isnullable=1 then ''√''else '''' end, 默认值=isnull(e.text,''''), 字段说明=isnull(g.[value],''''), 更新时间= getdate() FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties'' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 where d.name not like ''%20%'' order by a.id,a.colorder '--print @sqltextexec sp_executesql @sqltextfetch next from mycursor into @dbnameendclose mycursordeallocate mycursor