sqlserver数据库数据字典生成器

知识点：

1获取数据库结构

2添加表说明

3添加字段说明

4导出excel

 

源码：

https://files.cnblogs.com/files/a735882640/20180825DownTest.zip

说明文档：

https://files.cnblogs.com/files/a735882640/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%95%B0%E6%8D%AE%E5%AD%97%E5%85%B8%E7%94%9F%E6%88%90%E5%B7%A5%E5%85%B7%E8%AF%B4%E6%98%8E.zip

 

主要代码：

--1)查询所有表

 select * from (select top 100000000 ROW_NUMBER() OVER(ORDER BY m.name) as '序号',m.name as '表名',
(SELECT isnull(A.value,'')as value FROM sys.extended_properties A
LEFT JOIN sys.columns B ON A.major_id=B.object_id AND A.minor_id=B.column_id
LEFT JOIN sys.tables C ON A.major_id=C.object_id
WHERE A.class=1 AND C.name=m.name and isnull(B.name,'')='') as '表说明'
FROM sysobjects AS m
WHERE (m.type = 'u')
--and m.name='Fx_Taobao_Fxs'
--{2}
ORDER BY m.name)A
--where A.序号>0 and A.序号<=100


--2)    查询数据库结构：


----查询结构
SELECT
    表名=case   when   a.colorder=1   then   d.name   else   ''   end,
    表说明=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],'')
FROM   syscolumns   a
    left join   systypes   b   on   a.xusertype=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='MERCHANDISECOMMENT'         --如果只查询指定表,加上此条件
order by a.id,a.colorder




--3)    添加表说明sql语句：
--------------------------[Account_Type]  添加表说明：账号类型表
--删除旧说明
declare @name nvarchar(50)
select @name=a.name from   sys.extended_properties A
                            left join sys.columns B on A.major_id = B.object_id
                                                       and A.minor_id = B.column_id
                            left join sys.tables C on A.major_id = C.object_id
                     where  A.class = 1
                            and C.name = 'Account_Type'
                            and isnull(B.name, '') = '' 
print @name        
if(@name<>'')                    
begin
    execute sp_dropextendedproperty @name,'user','dbo','table','Account_Type',null,null;
end    

--添加新说明
if exists ( select  *
            from    sysobjects
            where   id = object_id(N'[Account_Type]')
                    and objectproperty(id, N'IsUserTable') = 1 )
    and not exists ( select isnull(B.name, '') as name ,
                            A.value
                     from   sys.extended_properties A
                            left join sys.columns B on A.major_id = B.object_id
                                                       and A.minor_id = B.column_id
                            left join sys.tables C on A.major_id = C.object_id
                     where  A.class = 1
                            and C.name = 'Account_Type'
                            and isnull(B.name, '') = '' )
    begin
        execute sp_addextendedproperty N'MS_Description', N'账号类型表', N'user',
            N'dbo', N'table', N'Account_Type', null, null; 
    end;                         
go    




---4)    添加字段说明sql语句：

--------------------------[Account_Type].[Type_Id] 添加字段说明：类别Id
--删除旧说明
declare @name nvarchar(50)
select @name=a.name from   sys.extended_properties A
                            left join sys.columns B on A.major_id = B.object_id
                                                       and A.minor_id = B.column_id
                            left join sys.tables C on A.major_id = C.object_id
                     where  A.class = 1
                            and C.name = 'Account_Type'
                            and isnull(B.name, '') = 'Type_Id' 
print @name        
if(@name<>'')                    
begin
    execute sp_dropextendedproperty @name,'user','dbo','table','Account_Type','column','Type_Id';  
end    
--添加新说明
if exists ( select top 1
                        1
                from    INFORMATION_SCHEMA.COLUMNS
                where   [TABLE_NAME] = 'Account_Type'
                        and [COLUMN_NAME] = 'Type_Id' )
    and not exists ( select isnull(B.name, '') as name ,
                            A.value
                     from   sys.extended_properties A
                            left join sys.columns B on A.major_id = B.object_id
                                                       and A.minor_id = B.column_id
                            left join sys.tables C on A.major_id = C.object_id
                     where  A.class = 1
                            and C.name = 'Account_Type'
                            and isnull(B.name, '') = 'Type_Id' )
    begin 
        exec sp_addextendedproperty N'MS_Description', N'类别Id', N'user',
            N'dbo', N'table', N'Account_Type', N'column', N'Type_Id';
    end; 
go