mssql生成数据库字典脚本-MarkDown

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <mrlm>
-- Create date: <2015-3-13>
-- Description: <生成数据库字典>
-- =============================================

SET NOCOUNT ON;
/*
*输出头部信息
*/
PRINT '# ' + DB_NAME()+ ' 数据库字典';
PRINT '';
DECLARE @tbCount INT
SELECT @tbCount=COUNT(0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG =DB_NAME();
PRINT '生成于：'+ CONVERT(VARCHAR, GETDATE(), 120)+',总计'+CAST(@tbCount as VARCHAR(4))+'张表';
/*
*数据库菜单列表
*/
--数据库表
DECLARE @tableDBTABLE TABLE
(
id INT IDENTITY(1, 1)
PRIMARY KEY ,
tableName NVARCHAR(300)
);

INSERT INTO @tableDBTABLE
( tableName
)
SELECT DISTINCT
TABLE_NAME AS tableName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( SELECT COUNT(*)
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id
AND 1 = idx.is_primary_key
LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id
AND idxcol.column_id = clmns.column_id
AND idxcol.object_id = clmns.object_id
AND 0 = idxcol.is_included_column
LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id
AND typ.user_type_id = typ.system_type_id
LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id
AND exprop.minor_id = clmns.column_id
AND exprop.name = 'MS_Description'
WHERE ( tbl.name = TABLE_NAME
AND exprop.class = 1
)
) > 0
ORDER BY TABLE_NAME;

--输出表目录信息
DECLARE @tname_cur VARCHAR(200);
DECLARE @count_t1 INT;
DECLARE @i_t1 INT;
DECLARE @i_t2 INT;

DECLARE @字段名称 VARCHAR(200);
DECLARE @类型 VARCHAR(200);
DECLARE @长度 VARCHAR(200);
DECLARE @数值精度 VARCHAR(200);
DECLARE @小数位数 VARCHAR(200);
DECLARE @默认值 VARCHAR(200);
DECLARE @允许为空 VARCHAR(200);
DECLARE @外键 VARCHAR(200);
DECLARE @主键 VARCHAR(200);
DECLARE @描述 VARCHAR(4000);

--初始化值
SET @i_t1 = 1;
SET @i_t2 = 1;
SELECT @count_t1 = COUNT(*)
FROM @tableDBTABLE;
--输出表行信息
IF @count_t1 > 0
BEGIN
SET @i_t1 = 1;
WHILE @i_t1 <= @count_t1
BEGIN
SELECT @tname_cur = tableName
FROM @tableDBTABLE
WHERE id = @i_t1;

DECLARE @tabledesc NVARCHAR(4000);--表描述
SELECT @tabledesc = CAST(value AS VARCHAR(4000))
FROM sys.extended_properties AS A
WHERE A.major_id = OBJECT_ID(@tname_cur)
AND name = 'MS_Description'
AND minor_id = 0;
SET @tabledesc = CASE WHEN ( @tabledesc IS NULL
OR LEN(RTRIM(LTRIM(@tabledesc))) <= 0
) THEN ' '
ELSE ' : ' + @tabledesc
END;

--输出表头部信息
DECLARE @cCount INT
SET @cCount=0
SELECT @cCount= COUNT(0) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tname_cur;
SET @cCount=@cCount-5;--减去统一列数量
PRINT '## ' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur+'('+CAST(@cCount as VARCHAR(4))+')'
+ ' 表 ' + @tabledesc;
PRINT '';

PRINT '|序号|字段名称|类型|长度|数值精度|小数位数|默认值|允许为空|描述|';
PRINT '|-|-|-|-|-|-|-|-|-|';

DECLARE TRows CURSOR
FOR
SELECT CAST(clmns.name AS VARCHAR(35))
,
+ CAST(udt.name AS CHAR(15))
,
+ CAST(CAST(CASE WHEN typ.name IN ( N'nchar',
N'nvarchar' )
AND clmns.max_length <> -1
THEN clmns.max_length / 2
ELSE clmns.max_length
END AS INT) AS VARCHAR(20))
,
+CAST(CAST(clmns.precision AS INT) AS VARCHAR(20))
,
+ CAST(CAST(clmns.scale AS INT) AS VARCHAR(20))
,
+ ISNULL(CAST(cnstr.definition AS VARCHAR(20)), '')
,
+ ( CASE WHEN clmns.is_nullable = 1 THEN '√'
ELSE ''
END )
,
+ ( CASE WHEN clmns.is_computed = 1 THEN '√'
ELSE ''
END )
,
+ ( CASE WHEN clmns.is_identity = 1 THEN '√'
ELSE ''
END )
,
+ ISNULL(CAST(exprop.value AS VARCHAR(500)), '')
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id
LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id
AND 1 = idx.is_primary_key
LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id
AND idxcol.column_id = clmns.column_id
AND idxcol.object_id = clmns.object_id
AND 0 = idxcol.is_included_column
LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id
AND typ.user_type_id = typ.system_type_id
LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id
AND exprop.minor_id = clmns.column_id
AND exprop.name = 'MS_Description'
WHERE ( tbl.name = @tname_cur
AND exprop.class = 1
)
ORDER BY clmns.column_id ASC;

SET @i_t2 = 1;
OPEN TRows;
FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度, @小数位数, @默认值,
@允许为空, @外键, @主键, @描述;

--输出表行数据
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '|' + CAST(@i_t2 AS VARCHAR(10))
+ '|'+ @字段名称
+ '|'+ @类型
+ '|'+ @长度
+ '|'+ @数值精度
+ '|'+ @小数位数
+ '|'+ @默认值
+ '|'+ @允许为空
+ '|'+ @描述
+'|'
FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度,
@小数位数, @默认值, @允许为空, @外键, @主键, @描述;
SET @i_t2 = @i_t2 + 1;
END;
CLOSE TRows;
DEALLOCATE TRows;
PRINT '';

SET @i_t1 = @i_t1 + 1;
END;
END;
SET NOCOUNT OFF;