สร้าง Data Dictionary แบบด่วนๆ

phunsanit

8 years ago

Updateอ่าน สร้าง Data Dictionary แค่คลิก แทนครับ

ต้องเขียน Data Dictionary ให้โครงการที่ทำอยู่ แต่มันมีหลายตารางมาก และที่สำคัญคือ แต่ละตารางจะมี relation ยุบยับเต็มไปหมด copy โครงสร้างมาแต่ละตารางมาวางในเอ็กส์เซล์ที่ละตัวก็ตาลาย พลาดได้ง่ายๆเลย

วิธีที่คิดออกคือ มันต้องมีโปรแกรมที่ช่วยงานนี้ได้ซิ เราไม่ใช่คนแรกที่ต้องเขียน Data Dictionary ซะหน่อย มีจริงๆ แต่แพง ไม่ก็ดูแปลกๆ เปลี่ยนไปใช้อีกวิธีคือ ทำไมไม่ให้ SQL ทำให้ละ search เจอ SQL Server Data Dictionary แต่มันก็ดูยาก เลยต้องเอามาเขียนใหม่ซะเอง

query ที่เขียนขึ้นมาใหม่คือ

SET ANSI_DEFAULTS OFF;
GO

SET NOCOUNT ON;
GO

SET TEXTSIZE 8192;
GO

PRINT '<HTML><body>'
PRINT '<!doctype html>'
PRINT '<html>'
PRINT '<head>'
PRINT '<meta charset="utf-8">'
PRINT '<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>'
PRINT '<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>'
PRINT '</head>'
PRINT '<body>'
PRINT '<h1>' + DB_NAME() + '</h1>'
PRINT '<h3>create date : ' + convert(VARCHAR(10), GETDATE(), 111) + '</h3>'

SELECT html
	,html1
	,html2
FROM (
	SELECT s.[name] AS [schema]
		,CASE
			WHEN s.[name] = 'dbo'
				THEN t.[name]
			ELSE s.[name] + '.' + t.[name]
			END AS [table]
		,NULL AS column_id
		,CAST('<table><cption><b>' + t.[name] + '</b>' + ISNULL(' : '+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</caption>' AS NTEXT) AS html
		,CAST('<thead>' + '<td style="width:100px;"><b>Primary Key</b></td>' + '<td style="width:200px;"><b>Foreign key</b></td>' + '<td style="width:400px;"><b>Column Name</b></td>' + '<td><b>Description</b></td>' AS NTEXT) AS html1
		,CAST('<td style="width:100px;"><b>Data Type</b></td>' + '<td style="width:100px;"><b>Allow Nulls</b></td>' + '<td style="width:100px;"><b>Identity</b></td>' + '<td style="width:100px;"><b>Default Value</b></td>' + '</thead><tbody>' AS NTEXT) AS html2
	FROM sys.tables t
	INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
	-- get description of table, if available
	LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
		AND ep.minor_id = 0
		AND ep.NAME = 'MS_Description'
		AND ep.class = 1
	WHERE t.is_ms_shipped = 0
		AND NOT EXISTS (
			SELECT *
			FROM sys.extended_properties ms
			WHERE ms.major_id = t.[object_id]
				AND ms.minor_id = 0
				AND ms.class = 1
				AND ms.[name] = 'microsoft_database_tools_support'
			)

	UNION ALL

	SELECT s.[name] AS [schema]
		,CASE
			WHEN s.[name] = 'dbo'
				THEN t.[name]
			ELSE s.[name] + '.' + t.[name]
			END AS [table]
		,c.column_id
		,'' AS html
		,'<tr><td>' + CASE
			WHEN pk.column_id IS NOT NULL
				THEN 'PK'
			ELSE ''
			END + '</td><td>' + CASE
			WHEN fk.primary_table IS NOT NULL
				THEN fk.primary_table + '.' + fk.primary_column
			ELSE ''
			END + '</td><td>' + c.[name] + '</td><td>' + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</td><td>' + CASE
			WHEN uty.[name] IS NOT NULL
				THEN uty.[name]
			ELSE ''
			END + CASE
			WHEN uty.[name] IS NOT NULL
				AND sty.[name] IS NOT NULL
				THEN ' (
			'
			ELSE ''
			END + CASE
			WHEN sty.[name] IS NOT NULL
				THEN sty.[name]
			ELSE ''
			END + CASE
			WHEN sty.[name] IN (
					'CHAR'
					,'NCHAR'
					,'VARCHAR'
					,'NVARCHAR'
					,'BINARY'
					,'VARBINARY'
					)
				THEN ' (
				' + CASE
						WHEN c.max_length = - 1
							THEN 'max'
						ELSE CASE
								WHEN sty.[name] IN (
										'NCHAR'
										,'NVARCHAR'
										)
									THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
								ELSE CAST(c.max_length AS VARCHAR(MAX))
								END
						END + '
				) '
			WHEN sty.[name] IN (
					'NUMERIC'
					,'DECIMAL'
					)
				THEN ' (
				' + CAST(c.precision AS VARCHAR(MAX)) + '
				,' + CAST(c.scale AS VARCHAR(MAX)) + '
				) '
			ELSE ''
			END + CASE
			WHEN uty.[name] IS NOT NULL
				AND sty.[name] IS NOT NULL
				THEN '
			) '
			ELSE ''
			END + '</td><td>' + CASE
			WHEN c.is_nullable = 1
				THEN 'Y'
			ELSE ''
			END + '</td><td>' AS html1
		,CASE
			WHEN c.is_identity = 1
				THEN 'Y'
			ELSE ''
			END + '</td><td>' + ISNULL(dc.[definition], '') + '</td></tr>' AS html2
	FROM sys.columns c
	INNER JOIN sys.tables t ON t.[object_id] = c.[object_id]
	INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
	-- get name of user data type
	LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
		AND uty.user_type_id = c.user_type_id
		AND c.user_type_id <> c.system_type_id
	-- get name of system data type
	LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
		AND sty.user_type_id = c.system_type_id
	-- get description of column, if available
	LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
		AND ep.minor_id = c.column_id
		AND ep.[name] = 'MS_Description'
		AND ep.[class] = 1
	-- get default' s code TEXT
	LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = t.[object_id]
		AND dc.parent_column_id = c.column_id
	-- check for inclusion in primary key
	LEFT OUTER JOIN (
		SELECT ic.column_id
			,i.[object_id]
		FROM sys.indexes i
		INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
			AND ic.[object_id] = i.[object_id]
		WHERE i.is_primary_key = 1
		) pk ON pk.column_id = c.column_id
		AND pk.[object_id] = t.[object_id]
	-- check for inclusion in foreign key
	LEFT OUTER JOIN (
		SELECT CASE
				WHEN s.[name] = 'dbo'
					THEN pk.[name]
				ELSE s.[name] + '.' + pk.[name]
				END AS primary_table
			,pkc.[name] AS primary_column
			,fkc.parent_object_id
			,fkc.parent_column_id
		FROM sys.foreign_keys fk
		INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
		INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
		INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
			AND fkc.referenced_object_id = pk.[object_id]
		INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
			AND pkc.column_id = fkc.referenced_column_id
		) fk ON fk.parent_object_id = t.[object_id]
		AND fk.parent_column_id = c.column_id
	WHERE t.is_ms_shipped = 0
		AND NOT EXISTS (
			SELECT *
			FROM sys.extended_properties ms
			WHERE ms.major_id = t.[object_id]
				AND ms.minor_id = 0
				AND ms.class = 1
				AND ms.[name] = 'microsoft_database_tools_support'
			)

	UNION ALL

	SELECT s.[name] AS [schema]
		,CASE
			WHEN s.[name] = 'dbo'
				THEN t.[name]
			ELSE s.[name] + '.' + t.[name]
			END AS [table]
		,999999 AS column_id
		,'' AS html
		,'' AS html1
		,'</tbody></table><br><hr><br>' AS html2
	FROM sys.tables t
	INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
	WHERE t.is_ms_shipped = 0
		AND NOT EXISTS (
			SELECT *
			FROM sys.extended_properties ms
			WHERE ms.major_id = t.[object_id]
				AND ms.minor_id = 0
				AND ms.class = 1
				AND ms.[name] = 'microsoft_database_tools_support'
			)
	) tmp
ORDER BY tmp.[schema]
	,tmp.[table]
	,tmp.column_id;

PRINT '<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit (Phunsanit@hotmail.com)</a>'
PRINT '</body>'
PRINT '</html>'

query ตัวนี้มีอะไรพิเศษมากกว่าตัวอื่นๆ หลายอย่าง คือ

การเอาไปใช้ แทนที่จะดูผลลัพธ์ในรูปแบบ grid ต้องใช้มันในแบบ Results to Text โดยกด (Ctrl+T) แล้วเอาผลที่ได้ (เป็น code html) ให้เอาไป copy เป็นไฟล์ .html แล้วจะ copy ไปลง excel / word อีกต่อหนึ่งก็ได้ ถ้าใช้ grid ก็ได้ code เหมือนกันแต่จะไม่สวย เพราะพวกคำสั่ง print มันจะไม่ทำงาน
ผลลัพท์ต้องแบ่งเป็น column html, html1, html2 เพราะว่าถ้าผลลัพธ์ที่ออกมากมันยาว จะโดนตัดทิ้งไปเฉยๆ พยามแก้อยู่นาน นานกว่าเขียน code ของมันซะอีก โดยลองใช้ SET TEXTSIZE 8192; และ cast ร่วมกับตัวเลือก Maximum number of characters display in each column ก็ไม่ได้ผล เลยต้องซอย column ออกมาแทน
การเอา code ที่เอาไปใช้ ถ้ามีการจัด code ใหม่ต้องระวังเงื่อนไข อย่าง ‘CHAR’ ถ้าโดนจัดเป็น ‘ CHAR ‘ (มี space) มันจะไม่ทำงาน
การใช้งานถ้าจะให้ข้อมูลออกมาครบต้องทำ table relation และใส่ descriptions ให้ครบ ถึงจะออกมาสวยๆ ครบๆ เหมือนกัน
sub query ด้านล่างๆ จริงๆ คิดว่าถ้าให้มีประสิทธิภาพน่าจะเขียนวิธีอื่นๆ ได้ แต่ตอนนี้ขอใช้ quick and easy (dirty) ไว้ก่อน
ยังมีจุดที่จะแก้ในครั้งต่อไปอย่าง มันยังมี result header อยู่ทำให้มีเส้น ———————- กับ html html1 html2 เกินมาอยู่ แต่ลบเองไม่กี่วิก็ใช้ได้ละ เพราะงั้นปล่อยๆ มันไปก่อน
ใครแก้จุดไหนได้ รบกวนบอกผมด้วยละกันครับ

ตัวอย่างผลลัพธ์ที่ได้

DATABASE NAME

create date : 2016/05/30

areas_cities : อำเภอ

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK		city_id		int
		status		tinyint	Y
		geo_id	ภูมิภาค	int			(‘0’)
		province_id		int			(‘0’)
		amphur_code		varchar (4)	Y

areas_cities_lang : อำเภอ (แปล)

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK	areas_cities.city_id	city_id		int
PK		language		char (2)
		name		nvarchar (600)

จริงๆ มันมีสีสันด้วยนะ สวยจนเอาไปใช้ได้เลยไม่ต้องแต่งอะไรอีก