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;