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%

知识点：

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 Aleft JOIN sys.columns B ON A.major_ID=B.object_ID AND A.minor_ID=B.column_IDleft JOIN sys.tables C ON A.major_ID=C.object_IDWHERE A.class=1 AND C.name=m.name and isnull(B.name,‘‘)=‘‘) as ‘表说明‘FROM sysobjects AS mWHERE (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,‘PRECISION‘),小数位数=isnull(ColUMNPROPERTY(a.ID,‘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=0where 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;     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,‘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,‘‘) = ‘Type_ID‘ )    begin         exec sp_addextendedproperty N‘MS_Description‘,N‘类别ID‘,N‘column‘,N‘Type_ID‘;    end; go

总结

以上是内存溢出为你收集整理的sqlserver数据库数据字典生成器全部内容，希望文章能够帮你解决sqlserver数据库数据字典生成器所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。