数据库文档生成工具(1)

页面导航:首页 > 数据库 > MsSql > 数据库文档生成工具(1)

数据库文档生成工具(1)

来源: 作者: 时间:2016-01-20 09:48 【

前端时间,有个同事离职了。然后在交接的过程中,数据库文档的缺乏一直是我心中的一个梗。然后就花了些时间写了个小工具。先上效果吧。界面效果:因为视图、存储过程、函数、
前端时间,有个同事离职了。然后在交接的过程中,文档的缺乏一直是我心中的一个梗。然后就花了些时间写了个小工具。先上效果吧。
界面效果:
 
因为视图、存储过程、函数、触发器的描述在数据库里是找不到的,所以我是新建了个数据库专门用来存放这些描述(默认第一次的时候函数的描述基本都是空的,之后从数据库中去取)




下面放一些关键的SQL代码:
 
获取某个数据库里的所有表:
 
select newid() as Id,isnull(f.value,'') as Remark,'Table' as ObjectType,
           Row_Number() over ( order by t.id )  as SortId,t.id as ObjectId,t.name as ObjectName,'' as Contents
           from   sysobjects  as t left outer  join sys.extended_properties   f  on t.id = f.major_id 
          and f.minor_id = 0 where t.xtype = 'U' AND t.status >= 0 and t.name<>'sysdiagrams'
 
获取某个数据库里的所有表字段:
 
SELECT TOP 100 PERCENT 
                    newid() as Id,
                    d.id as ColumnsObjectId,
                    d.name as ColumnsObjectName,
                    a.id as ColumnsId, 
                    a.name AS ColumnsName,  
                    b.name AS ColumnsType,  
                    a.length AS ColumnsLength, 
                    ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS DecimaPlaces,  
                    CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity') = 1 THEN '是' ELSE '' END AS Mark,  
                    CASE WHEN EXISTS 
                        (SELECT 1 FROM dbo.sysindexes si  
                          INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid   
                          INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid   
                          INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'  
                       WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS Primarykey,   
                    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS IsEmpty,  
                     ISNULL(e.text, '') AS Defaults,  
                        ISNULL(g.[value], '') AS ColumnsRemark,
                    a.colorder AS ColumnsSortId
              FROM dbo.syscolumns a  
                    LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype  
                    INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 
                    LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id  
                    LEFT OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = 'MS_Description'  
                   LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description'  
              ORDER BY d.name, ColumnsSortId

 

获取外键:
 
SELECT b.rkey  as ColumnsId,
            (SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)  as ColumnsName,
              b.rkeyid as TabaleId,OBJECT_NAME(b.rkeyid) as TableName
           ,b.fkey   as ForeignKeyId
            ,(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) as ForeignKeyName,
            b.fkeyid as ForeignKeyTableId
            ,object_name(b.fkeyid)    AS ForeignKeyTableName
            ,ObjectProperty(a.id,'CnstIsUpdateCascade') as CnstIsUpdateCascade   
            ,ObjectProperty(a.id,'CnstIsDeleteCascade')    as CnstIsDeleteCascade
        FROM sysobjects a   
            join sysforeignkeys b on a.id=b.constid   
             join sysobjects c on a.parent_obj=c.id   
        where a.xtype='f' AND c.xtype='U'   
            and b.rkeyid in (select id from sysobjects  as d where d.xtype = 'U' AND d.status >= 0 )
获取索引:
 
 
WITH tx AS
         (
                SELECT a.object_id
                       ,b.name AS schema_name
                      ,a.name AS table_name
                      ,c.name as ix_name
                      ,c.is_unique AS ix_unique
                       ,c.type_desc AS ix_type_desc
                       ,d.index_column_id
                      ,d.is_included_column
                      ,e.name AS column_name
                      ,f.name AS fg_name
                      ,d.is_descending_key AS is_descending_key
                      ,c.is_primary_key
                       ,c.is_unique_constraint
                  FROM sys.tables AS a
                  INNER JOIN sys.schemas AS b            ON a.schema_id = b.schema_id AND a.is_ms_shipped = 0
                 INNER JOIN sys.indexes AS c            ON a.object_id = c.object_id
                 INNER JOIN sys.index_columns AS d      ON d.object_id = c.object_id AND d.index_id = c.index_id
                 INNER JOIN sys.columns AS e            ON e.object_id = d.object_id AND e.column_id = d.column_id
                 INNER JOIN sys.data_spaces AS f        ON f.data_space_id = c.data_space_id
                where a.name<>'sysdiagrams'
         )
         SELECT
               Drop_Index   = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
                                   THEN 'ALTER TABLE ' + a.table_name + ' DROP CONSTRAINT ' + a.ix_name
                                   ELSE 'DROP INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name  END
              ,Create_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
                                    THEN 'ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name 
                                      + CASE WHEN a.is_primary_key = 1 THEN ' PRIMARY KEY' ELSE ' UNIQUE' END + '(' + indexColumns.ix_index_column_name + ')'
                                   ELSE 'CREATE ' + CASE WHEN a.ix_unique = 1 THEN 'UNIQUE ' ELSE '' END 
                                       + a.ix_type_desc + ' INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name
                                       + '.' + a.table_name + '(' + indexColumns.ix_index_column_name + ')'
                                        + CASE WHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')' ELSE '' END
                                       + ' ON [' + a.fg_name +']' END
              ,CASE WHEN a.ix_unique = 1 THEN 'UNIQUE' else '' END AS IXUnique
              ,case when a.ix_type_desc='CLUSTERED' then '聚集索引' when a.ix_type_desc='NONCLUSTERED' then '非聚集索引' else '' end as IXtype
              ,a.ix_name  as IXName
              ,a.object_id as TableId
              ,a.table_name as TableName
               ,indexColumns.ix_index_column_name as ColumnsName
               ,IncludeIndex.ix_included_column_name as IncludeColumnsName
              ,a.fg_name
              ,a.is_primary_key
              ,a.is_unique_constraint                                       
        FROM
         (
                 SELECT DISTINCT
                       ix_unique
                       ,ix_type_desc
                       ,object_id
                      ,ix_name
                       ,schema_name
                       ,table_name
                       ,fg_name
                      ,is_primary_key
                      ,is_unique_constraint
                  FROM tx
        ) AS a
        OUTER APPLY
         (
               SELECT ix_index_column_name
                      = STUFF((
                                SELECT ',' + column_name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END
                                  FROM tx AS b
                                  WHERE schema_name = a.schema_name
                                    AND table_name=a.table_name
                                    AND ix_name=a.ix_name
                                    AND ix_type_desc=a.ix_type_desc
                                   AND fg_name=a.fg_name
                                    AND is_included_column=0
                                 ORDER BY index_column_id
                                    FOR XML PATH('')
                                ),1,1,'')
        )IndexColumns
        OUTER APPLY
         (
                SELECT ix_included_column_name
                        = STUFF((
                                SELECT ',' + column_name
                                  FROM tx AS b
                                  WHERE schema_name = a.schema_name
                                   AND table_name=a.table_name
                                   AND ix_name=a.ix_name
                                   AND ix_type_desc=a.ix_type_desc
                                   AND fg_name=a.fg_name
                                   AND is_included_column=1
                                 ORDER BY index_column_id
                                   FOR XML PATH('')
                                ), 1,1,'')
        )IncludeIndex
         ORDER BY a.schema_name,a.table_name,a.ix_name

 

获取关联(表-视图-函数之前的关联):
 
 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
            from sys.sql_dependencies as s inner join sys.objects as o on s.object_id=o.object_id 
            inner join (select id,name,d.xtype from sysobjects  as d where  d.status >= 0) as t on t.id=referenced_major_id
            where t.name<>'sysdiagrams'
获取视图名称:

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

 

获取视图列:
 
 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,
             ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS DecimaPlaces,
             CASE WHEN COLUMNPROPERTY(c.id,c.name, 'IsIdentity') = 1 THEN '是' ELSE '' END AS Mark,
             CASE WHEN c.isnullable = 0 THEN '√' ELSE '' END AS Primarykey,
            CASE WHEN c.isnullable = 1 THEN '√' ELSE '' END AS IsEmpty,  
             '' AS Defaults,'' AS ColumnsRemark,
            c.colorder as ColumnsSortId
            From SysObjects As o , SysColumns As c , SysTypes As t
            Where o.type ='v' And o.id = c.id And c.xtype = t.xtype and t.name<>'sysname'
            Order By o.name ,c.colorder

 

获取函数/存储过程:
 

 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
            from  sys.objects  O LEFT OUTER JOIN sys.extended_properties E ON O.object_id = E.major_id
            left outer join sys.all_sql_modules on all_sql_modules.object_id=O.object_id
            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')
            ORDER BY O.name

 

 
获取函数/存储过程的参数:
 
 SELECT sp.object_Id as FunctionId, sp.name as FunctionName,
             isnull(param.name,'')as ParamName,isnull(usrt.name,'') AS [DataType],
             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],
             '' as ParamReamrk,isnull(parameter_id,0) as SortId
             FROM sys.objects AS sp  INNER JOIN sys.schemas b ON sp.schema_id = b.schema_id
             left outer JOIN sys.all_parameters AS param ON param.object_id=sp.object_Id
             LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
             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)) 
           LEFT OUTER JOIN sys.extended_properties E ON sp.object_id = E.major_id
            WHERE sp.TYPE in ('FN', 'IF', 'TF','P')  AND ISNULL(sp.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
             ORDER BY sp.name,param.parameter_id ASC

 

获取触发器:
 
select  t.object_id as TriggersId, t.name as TriggersName,t.create_date as Createtime,t.type as FunctionType,
         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
        on  t.object_id=m.object_id 
         inner join  sys.objects  as sp on t.parent_id=sp.object_id

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<