sqlserver 获取实例上用户数据库的数据字典

原理很简单：将获取数据字典信息（通过动态视图获取）存入到目标表（数据字典表）中即可。

本人自用实例

1）创建相关的字典表

use YWMonitor
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
if 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'
GO

SET ANSI_PADDING OFF
GO

 

2）将获取的字典信息录入表中

declare @dbname nvarchar(500),@sqltext nvarchar(2000)
declare  mycursor cursor for
select name from sys.databases where database_id >4 and name not in ('xxxDB') and state_desc = 'ONLINE'
open mycursor
fetch next from mycursor into @dbname
while @@FETCH_STATUS=0
begin 
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 @sqltext
exec sp_executesql @sqltext
fetch next from mycursor into @dbname
end

close mycursor
deallocate mycursor

 

转载于:https://www.cnblogs.com/lx823706/p/6274369.html