你的位置:首页 > 数据库

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


  前端时间,有个同事离职了。然后在交接的过程中,数据库文档的缺乏一直是我心中的一个梗。然后就花了些时间写了个小工具。先上效果吧。

界面效果:

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

下面放一些关键的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 Contents3       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'

View Code

获取某个数据库里的所有表字段:

 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 ColumnsSortId21        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

View Code

获取外键:

 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 CnstIsDeleteCascade10     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 )

View Code

获取索引:

 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_id10            ,d.is_included_column11            ,e.name AS column_name12            ,f.name AS fg_name13            ,d.is_descending_key AS is_descending_key14            ,c.is_primary_key15            ,c.is_unique_constraint16          FROM sys.tables AS a17         INNER JOIN sys.schemas AS b      ON a.schema_id = b.schema_id AND a.is_ms_shipped = 018         INNER JOIN sys.indexes AS c      ON a.object_id = c.object_id19         INNER JOIN sys.index_columns AS d   ON d.object_id = c.object_id AND d.index_id = c.index_id20         INNER JOIN sys.columns AS e      ON e.object_id = d.object_id AND e.column_id = d.column_id21         INNER JOIN sys.data_spaces AS f    ON f.data_space_id = c.data_space_id22         where a.name<>'sysdiagrams'23     )24     SELECT25        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_name27                  ELSE 'DROP INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name END28        ,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_name33                    + '.' + 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 '' END35                    + ' ON [' + a.fg_name +']' END36        ,CASE WHEN a.ix_unique = 1 THEN 'UNIQUE' else '' END AS IXUnique37        ,case when a.ix_type_desc='CLUSTERED' then '聚集索引' when a.ix_type_desc='NONCLUSTERED' then '非聚集索引' else '' end as IXtype38        ,a.ix_name as IXName39        ,a.object_id as TableId40        ,a.table_name as TableName41        ,indexColumns.ix_index_column_name as ColumnsName42        ,IncludeIndex.ix_included_column_name as IncludeColumnsName43        ,a.fg_name44        ,a.is_primary_key45        ,a.is_unique_constraint                    46     FROM47     (48         SELECT DISTINCT49             ix_unique50            ,ix_type_desc51            ,object_id52            ,ix_name53            ,schema_name54            ,table_name55            ,fg_name56            ,is_primary_key57            ,is_unique_constraint58          FROM tx59     ) AS a60     OUTER APPLY61     (62         SELECT ix_index_column_name63            = STUFF((64                 SELECT ',' + column_name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END65                  FROM tx AS b66                 WHERE schema_name = a.schema_name67                  AND table_name=a.table_name68                  AND ix_name=a.ix_name69                  AND ix_type_desc=a.ix_type_desc70                  AND fg_name=a.fg_name71                  AND is_included_column=072                 ORDER BY index_column_id73                  FOR '')74                 ),1,1,'')75     )IndexColumns76     OUTER APPLY77     (78         SELECT ix_included_column_name79            = STUFF((80                 SELECT ',' + column_name81                  FROM tx AS b82                 WHERE schema_name = a.schema_name83                  AND table_name=a.table_name84                  AND ix_name=a.ix_name85                  AND ix_type_desc=a.ix_type_desc86                  AND fg_name=a.fg_name87                  AND is_included_column=188                 ORDER BY index_column_id89                  FOR '')90                 ), 1,1,'')91     )IncludeIndex92     ORDER BY a.schema_name,a.table_name,a.ix_name

View Code

获取关联(表-视图-函数之前的关联):

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 ModifyDate2       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_id4       where t.name<>'sysdiagrams'

View Code

获取视图名称:

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

View Code

获取视图列:

 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

View Code

获取函数/存储过程:

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 FunctionReamrk2       from sys.objects O LEFT OUTER JOIN sys.extended_properties E ON O.object_id = E.major_id3       left outer join sys.all_sql_modules on all_sql_modules.object_id=O.object_id4       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

View Code

获取函数/存储过程的参数:

 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_id10       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

View Code

获取触发器:

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 m3     on t.object_id=m.object_id 4     inner join sys.objects as sp on t.parent_id=sp.object_id

View Code

以上部分代码摘自博客园,部分代码摘自国外不知名人士。但忘了是谁了!如有知情者,欢迎回复!

对于上面的一些图上都是马赛克,我只能表示抱歉!

如果你对于这些SQL语句有更好的实现方式,欢迎评论、小纸条。