[SQL]將資料庫表格定義輸出成網頁

2013-04-26

2138
0
SQL

[SQL]將資料庫表格定義輸出成網頁

參考過下列資料後

修改一下內容如下：


-- 使用前請看原始參考資料操作
-- http://www.cnblogs.com/netWild/archive/2013/04/19/3031734.html

-- 以下的SQL有調整過與原版不同
SET NOCOUNT ON
DECLARE @TableName nvarchar(35)
DECLARE Tbls CURSOR
FOR
SELECT DISTINCT name
FROM
	sys.tables
WHERE
	type = 'U'
ORDER BY
	name
OPEN Tbls
PRINT N'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
PRINT N'<html xmlns="http://www.w3.org/1999/xhtml">'
PRINT N'<head>'
PRINT N'<title>資料庫表格定義['+db_name()+']</title>'
PRINT N'<style type="text/css">'
PRINT N'body{margin:0; font:11pt "arial", "標楷體"; cursor:default;}'
PRINT N'.tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'
PRINT N'.tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#45360A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'
PRINT N'.tableBox table {width:1000px; padding:0px }'
PRINT N'.tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#F7EBC8; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
PRINT N'.tableBox td {height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
PRINT N'</style>'
PRINT N'</head>'
PRINT N'<body>'
PRINT N'<div class="tableBox">'
PRINT N'<table>'
PRINT N'<tr>'
PRINT N'<th>資料庫名稱</th>'
PRINT N'<th>產生日期</th>'
PRINT N'</tr>'
PRINT N'<tr>'
PRINT N'<td>' + db_name() + '</td>'
PRINT N'<td>' + convert(char(8),getdate(),112) + '</td>'
PRINT N'</tr>'
PRINT N'</table>'
PRINT N'</div>'
FETCH NEXT FROM Tbls
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT N'<div class="tableBox">'
SELECT '<h3>' + @TableName + ' : ' + cast(Value AS VARCHAR(1000)) + '</h3>'
FROM
	sys.extended_properties A
WHERE
	A.major_id = object_id(@TableName)
	AND name = 'MS_Description'
	AND minor_id = 0
    PRINT N'<h3>' + @TableName + '</h3>'
    PRINT N'<table cellspacing="0">'
    --Get the Description of the table
    --Characters 1-250
    PRINT N'<tr>' --Set up the Column Headers for the Table
    PRINT N'<th>欄位名稱</th>'
    PRINT N'<th>描述</th>'
    PRINT N'<th>主鍵</th>'
    PRINT N'<th>外鍵</th>'
    PRINT N'<th>類型</th>'
    PRINT N'<th>長度</th>'
    PRINT N'<th>數值精度</th>'
    PRINT N'<th>小數位數</th>'
    PRINT N'<th>允許為空</th>'
    PRINT N'<th>計算列</th>'
    PRINT N'<th>標識列</th>'
    PRINT N'<th>預設值</th>'
--Get the Table Data
SELECT '</tr><tr>'
	 , '<td>' + cast(clmns.name AS VARCHAR(35)) + '</td>'
	 , '<td>' + isnull(cast(exprop.value AS VARCHAR(500)), '') + '</td>'
	 , '<td>' + cast(isnull(idxcol.index_column_id, 0) AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(isnull((SELECT TOP 1 1
							 FROM
								 sys.foreign_key_columns AS fkclmn
							 WHERE
								 fkclmn.parent_column_id = clmns.column_id
								 AND fkclmn.parent_object_id = clmns.object_id), 0) AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(udt.name AS CHAR(15)) + '</td>'
	 , '<td>' + 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)) + '</td>'
	 , '<td>' + cast(cast(clmns.precision AS INT) AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(cast(clmns.scale AS INT) AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(clmns.is_nullable AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(clmns.is_computed AS VARCHAR(20)) + '</td>'
	 , '<td>' + cast(clmns.is_identity AS VARCHAR(20)) + '</td>'
	 , '<td>' + isnull(cast(cnstr.definition AS VARCHAR(20)), '') + '</td>'
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 = @TableName)
ORDER BY
	clmns.column_id ASC
    PRINT N'</tr></table>'
    PRINT N'</div>'
    FETCH NEXT FROM Tbls
    INTO @TableName
END
PRINT N'</body></HTML>'
CLOSE Tbls
DEALLOCATE Tbls

另外新增了取得Function、Stored Procedure、View的部份


SET NOCOUNT ON
DECLARE @SPNAME sysname
DECLARE Tbls CURSOR
FOR
SELECT DISTINCT [type_desc]
FROM
	(SELECT o.id
		  , o.name [sp_name]
		  , u.name [u_name]
		  , o.xtype
		  , CASE
				WHEN o.xtype IN ('P', 'FN', 'IF', 'TF') THEN
					'EXECUTE'
				WHEN o.xtype IN ('V') THEN
					'SELECT'
			END [GCMD]
		  , CASE
				WHEN o.xtype = 'P' THEN
					'Stored Procedure'
				WHEN o.xtype IN ('FN', 'IF', 'TF') THEN
					'Function'
				WHEN o.xtype IN ('V') THEN
					'View'
			END [type_desc]
	 FROM
		 sys.sysobjects o
		 JOIN sys.sysusers u
			 ON o.uid = u.uid
	 WHERE
		 o.xtype IN ('P', 'FN', 'IF', 'TF', 'V')
		 AND o.category = 0) t
ORDER BY
	t.type_desc
OPEN Tbls
PRINT N'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
PRINT N'<html xmlns="http://www.w3.org/1999/xhtml">'
PRINT N'<head>'
PRINT N'<title>Function、Stored Procedure、View['+db_name()+']</title>'
PRINT N'<style type="text/css">'
PRINT N'body{margin:0; font:11pt "arial", "標楷體"; cursor:default;}'
PRINT N'.tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'
PRINT N'.tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#45360A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'
PRINT N'.tableBox table {width:1000px; padding:0px }'
PRINT N'.tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#F7EBC8; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
PRINT N'.tableBox td {height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'
PRINT N'</style>'
PRINT N'</head>'
PRINT N'<body>'
PRINT N'<div class="tableBox">'
PRINT N'<table>'
PRINT N'<tr>'
PRINT N'<th>資料庫名稱</th>'
PRINT N'<th>產生日期</th>'
PRINT N'</tr>'
PRINT N'<tr>'
PRINT N'<td>' + db_name() + '</td>'
PRINT N'<td>' + convert(char(8),getdate(),112) + '</td>'
PRINT N'</tr>'
PRINT N'</table>'
PRINT N'</div>'
FETCH NEXT FROM Tbls
INTO @SPNAME
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT N'<div class="tableBox">'    
    PRINT N'<h3>' + @SPNAME + '</h3>'
    PRINT N'<table cellspacing="0">' 
    PRINT N'<tr>' --Set up the Column Headers for the Table
    PRINT N'<th>名稱</th>'

--Get the Table Data        
SELECT '</tr><tr>'
	 , '<td>' + [sp_name] + '</td>'
FROM
	(SELECT o.id
		  , o.name [sp_name]
		  , u.name [u_name]
		  , o.xtype
		  , CASE
				WHEN o.xtype IN ('P', 'FN', 'IF', 'TF') THEN
					'EXECUTE'
				WHEN o.xtype IN ('V') THEN
					'SELECT'
			END [GCMD]
		  , CASE
				WHEN o.xtype = 'P' THEN
					'Stored Procedure'
				WHEN o.xtype IN ('FN', 'IF', 'TF') THEN
					'Function'
				WHEN o.xtype IN ('V') THEN
					'View'
			END [type_desc]
	 FROM
		 sys.sysobjects o
		 JOIN sys.sysusers u
			 ON o.uid = u.uid
	 WHERE
		 o.xtype IN ('P', 'FN', 'IF', 'TF', 'V')
		 AND o.category = 0) t
WHERE
	type_desc = @SPNAME
ORDER BY
	t.type_desc
    PRINT N'</tr></table>'
    PRINT N'</div>'
    FETCH NEXT FROM Tbls
    INTO @SPNAME
END
PRINT N'</body></HTML>'
CLOSE Tbls
DEALLOCATE Tbls

輸出畫面

表格定義

Function、Stored Procedure、View(此資料庫中沒有使用者定義的function)

注意事項

SSMS要做下列調整

顯示資料行標頭要取消掉

以文字顯示結果

查詢結果要存成網頁(htm、html)

參考資料

實用技巧：利用SQL Server的擴展屬性自動生成數據字典

批次取得資料庫中全部的 Stored Procedure、View、Function

回首頁