UDN-企业互联网技术人气社区

板块导航

浏览  : 1374
回复  : 0

[讨论交流] 数据库前端开发工具——文档生成工具(1)

[复制链接]
白青青的头像 楼主
发表于 2016-5-27 14:17:08 | 显示全部楼层 |阅读模式
  花了些时间写了个小工具。先上效果吧。

  界面效果:
2.png

  因为视图、存储过程、函数、触发器的描述在数据库里是找不到的,所以我是新建了个数据库专门用来存放这些描述(默认第一次的时候函数的描述基本都是空的,之后从数据库中去取)
2.png

2.png

  下面放一些关键的SQL代码:

  获取某个数据库里的所有表:
  1. select newid() as Id,isnull(f.value,'') as Remark,'Table' as ObjectType,
  2.              Row_Number() over ( order by t.id )  as SortId,t.id as ObjectId,t.name as ObjectName,'' as Contents
  3.              from   sysobjects  as t left outer  join sys.extended_properties   f  on t.id = f.major_id
  4.              and f.minor_id = 0 where t.xtype = 'U' AND t.status >= 0 and t.name<>'sysdiagrams'
复制代码

  获取某个数据库里的所有表字段:
  1. SELECT TOP 100 PERCENT
  2.                     newid() as Id,
  3.                     d.id as ColumnsObjectId,
  4.                     d.name as ColumnsObjectName,
  5.                     a.id as ColumnsId,
  6.                     a.name AS ColumnsName,  
  7.                     b.name AS ColumnsType,  
  8.                     a.length AS ColumnsLength,
  9.                     ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS DecimaPlaces,  
  10.                     CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity') = 1 THEN '是' ELSE '' END AS Mark,  
  11.                     CASE WHEN EXISTS
  12.                         (SELECT 1 FROM dbo.sysindexes si  
  13.                           INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid   
  14.                           INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid   
  15.                           INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'  
  16.                        WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS Primarykey,   
  17.                     CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS IsEmpty,  
  18.                      ISNULL(e.text, '') AS Defaults,  
  19.                         ISNULL(g.[value], '') AS ColumnsRemark,
  20.                     a.colorder AS ColumnsSortId
  21.               FROM dbo.syscolumns a  
  22.                     LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype  
  23.                     INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
  24.                     LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id  
  25.                     LEFT OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = 'MS_Description'  
  26.                    LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description'  
  27.               ORDER BY d.name, ColumnsSortId
复制代码

  获取外键:
  1. SELECT b.rkey  as ColumnsId,
  2.             (SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)  as ColumnsName,
  3.               b.rkeyid as TabaleId,OBJECT_NAME(b.rkeyid) as TableName
  4.            ,b.fkey   as ForeignKeyId
  5.             ,(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) as ForeignKeyName,
  6.             b.fkeyid as ForeignKeyTableId
  7.             ,object_name(b.fkeyid)    AS ForeignKeyTableName
  8.             ,ObjectProperty(a.id,'CnstIsUpdateCascade') as CnstIsUpdateCascade   
  9.             ,ObjectProperty(a.id,'CnstIsDeleteCascade')    as CnstIsDeleteCascade
  10.         FROM sysobjects a   
  11.             join sysforeignkeys b on a.id=b.constid   
  12.              join sysobjects c on a.parent_obj=c.id   
  13.         where a.xtype='f' AND c.xtype='U'   
  14.             and b.rkeyid in (select id from sysobjects  as d where d.xtype = 'U' AND d.status >= 0 )
复制代码

  获取索引:
  1. WITH tx AS
  2.          (
  3.                 SELECT a.object_id
  4.                        ,b.name AS schema_name
  5.                       ,a.name AS table_name
  6.                       ,c.name as ix_name
  7.                       ,c.is_unique AS ix_unique
  8.                        ,c.type_desc AS ix_type_desc
  9.                        ,d.index_column_id
  10.                       ,d.is_included_column
  11.                       ,e.name AS column_name
  12.                       ,f.name AS fg_name
  13.                       ,d.is_descending_key AS is_descending_key
  14.                       ,c.is_primary_key
  15.                        ,c.is_unique_constraint
  16.                   FROM sys.tables AS a
  17.                   INNER JOIN sys.schemas AS b            ON a.schema_id = b.schema_id AND a.is_ms_shipped = 0
  18.                  INNER JOIN sys.indexes AS c            ON a.object_id = c.object_id
  19.                  INNER JOIN sys.index_columns AS d      ON d.object_id = c.object_id AND d.index_id = c.index_id
  20.                  INNER JOIN sys.columns AS e            ON e.object_id = d.object_id AND e.column_id = d.column_id
  21.                  INNER JOIN sys.data_spaces AS f        ON f.data_space_id = c.data_space_id
  22.                 where a.name<>'sysdiagrams'
  23.          )
  24.          SELECT
  25.                Drop_Index   = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
  26.                                    THEN 'ALTER TABLE ' + a.table_name + ' DROP CONSTRAINT ' + a.ix_name
  27.                                    ELSE 'DROP INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name  END
  28.               ,Create_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
  29.                                     THEN 'ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name
  30.                                       + CASE WHEN a.is_primary_key = 1 THEN ' PRIMARY KEY' ELSE ' UNIQUE' END + '(' + indexColumns.ix_index_column_name + ')'
  31.                                    ELSE 'CREATE ' + CASE WHEN a.ix_unique = 1 THEN 'UNIQUE ' ELSE '' END
  32.                                        + a.ix_type_desc + ' INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name
  33.                                        + '.' + a.table_name + '(' + indexColumns.ix_index_column_name + ')'
  34.                                         + CASE WHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')' ELSE '' END
  35.                                        + ' ON [' + a.fg_name +']' END
  36.               ,CASE WHEN a.ix_unique = 1 THEN 'UNIQUE' else '' END AS IXUnique
  37.               ,case when a.ix_type_desc='CLUSTERED' then '聚集索引' when a.ix_type_desc='NONCLUSTERED' then '非聚集索引' else '' end as IXtype
  38.               ,a.ix_name  as IXName
  39.               ,a.object_id as TableId
  40.               ,a.table_name as TableName
  41.                ,indexColumns.ix_index_column_name as ColumnsName
  42.                ,IncludeIndex.ix_included_column_name as IncludeColumnsName
  43.               ,a.fg_name
  44.               ,a.is_primary_key
  45.               ,a.is_unique_constraint                                       
  46.         FROM
  47.          (
  48.                  SELECT DISTINCT
  49.                        ix_unique
  50.                        ,ix_type_desc
  51.                        ,object_id
  52.                       ,ix_name
  53.                        ,schema_name
  54.                        ,table_name
  55.                        ,fg_name
  56.                       ,is_primary_key
  57.                       ,is_unique_constraint
  58.                   FROM tx
  59.         ) AS a
  60.         OUTER APPLY
  61.          (
  62.                SELECT ix_index_column_name
  63.                       = STUFF((
  64.                                 SELECT ',' + column_name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END
  65.                                   FROM tx AS b
  66.                                   WHERE schema_name = a.schema_name
  67.                                     AND table_name=a.table_name
  68.                                     AND ix_name=a.ix_name
  69.                                     AND ix_type_desc=a.ix_type_desc
  70.                                    AND fg_name=a.fg_name
  71.                                     AND is_included_column=0
  72.                                  ORDER BY index_column_id
  73.                                     FOR XML PATH('')
  74.                                 ),1,1,'')
  75.         )IndexColumns
  76.         OUTER APPLY
  77.          (
  78.                 SELECT ix_included_column_name
  79.                         = STUFF((
  80.                                 SELECT ',' + column_name
  81.                                   FROM tx AS b
  82.                                   WHERE schema_name = a.schema_name
  83.                                    AND table_name=a.table_name
  84.                                    AND ix_name=a.ix_name
  85.                                    AND ix_type_desc=a.ix_type_desc
  86.                                    AND fg_name=a.fg_name
  87.                                    AND is_included_column=1
  88.                                  ORDER BY index_column_id
  89.                                    FOR XML PATH('')
  90.                                 ), 1,1,'')
  91.         )IncludeIndex
  92.          ORDER BY a.schema_name,a.table_name,a.ix_name
复制代码

  获取关联(表-视图-函数之前的关联):
  1. select distinct t.id as TableId ,t.name as TableName,rtrim(t.xtype) as TableType ,o.object_id as RelatedId,rtrim(o.name) as RelatedName,o.type as RelatedType,o.type_desc as RelatedTypeDesc,o.create_date as CreateDate,o.modify_date as ModifyDate
  2.             from sys.sql_dependencies as s inner join sys.objects as o on s.object_id=o.object_id
  3.             inner join (select id,name,d.xtype from sysobjects  as d where  d.status >= 0) as t on t.id=referenced_major_id
  4.             where t.name<>'sysdiagrams'
  5. 获取视图名称:

  6. select newid() as Id,'' as Remark,'' as ObjectType,
  7.              Row_Number() over ( order by getdate() )  as SortId,v.object_id as ObjectId,v.name as ObjectName ,m.definition as Contents
  8.              from sys.views  v  left outer join sys.all_sql_modules as m on v.object_id=m.object_id
  9.             order by ObjectId
复制代码

  获取视图列:
  1. Select newid() as Id,c.id as ColumnsId,o.id as ColumnsObjectId,o.Name As ColumnsObjectName,c.name As ColumnsName, t.name As ColumnsType, c.length As ColumnsLength,
  2.              ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS DecimaPlaces,
  3.              CASE WHEN COLUMNPROPERTY(c.id,c.name, 'IsIdentity') = 1 THEN '是' ELSE '' END AS Mark,
  4.              CASE WHEN c.isnullable = 0 THEN '√' ELSE '' END AS Primarykey,
  5.             CASE WHEN c.isnullable = 1 THEN '√' ELSE '' END AS IsEmpty,  
  6.              '' AS Defaults,'' AS ColumnsRemark,
  7.             c.colorder as ColumnsSortId
  8.             From SysObjects As o , SysColumns As c , SysTypes As t
  9.             Where o.type ='v' And o.id = c.id And c.xtype = t.xtype and t.name<>'sysname'
  10.             Order By o.name ,c.colorder
复制代码

  获取函数/存储过程:
  1. select O.object_id as FunctionId, O.name as FunctionName,rtrim(O.type) as FunctionType,O.create_date as Createtime, definition as Contents,'' as FunctionReamrk
  2.             from  sys.objects  O LEFT OUTER JOIN sys.extended_properties E ON O.object_id = E.major_id
  3.             left outer join sys.all_sql_modules on all_sql_modules.object_id=O.object_id
  4.             WHERE  O.name IS NOT NULL  AND ISNULL(O.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'  AND O.type in ('FN', 'IF', 'TF','P')
  5.             ORDER BY O.name
复制代码

  获取函数/存储过程的参数:
  1. SELECT sp.object_Id as FunctionId, sp.name as FunctionName,
  2.              isnull(param.name,'')as ParamName,isnull(usrt.name,'') AS [DataType],
  3.              ISNULL(baset.name, '') AS [SystemType], CAST(CASE when baset.name is null then 0  WHEN baset.name IN ('nchar', 'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],
  4.              '' as ParamReamrk,isnull(parameter_id,0) as SortId
  5.              FROM sys.objects AS sp  INNER JOIN sys.schemas b ON sp.schema_id = b.schema_id
  6.              left outer JOIN sys.all_parameters AS param ON param.object_id=sp.object_Id
  7.              LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
  8.              LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
  9.            LEFT OUTER JOIN sys.extended_properties E ON sp.object_id = E.major_id
  10.             WHERE sp.TYPE in ('FN', 'IF', 'TF','P')  AND ISNULL(sp.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
  11.              ORDER BY sp.name,param.parameter_id ASC
复制代码

  获取触发器:
  1. select  t.object_id as TriggersId, t.name as TriggersName,t.create_date as Createtime,t.type as FunctionType,
  2.          parent_id as TableId,sp.name as TableName,definition as Contents,'' as TriggersRemark from sys.triggers as t inner join sys.all_sql_modules as m
  3.         on  t.object_id=m.object_id
  4.          inner join  sys.objects  as sp on t.parent_id=sp.object_id
复制代码

相关帖子

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部