Tag Archive ดาต้าดิกชันนารี

Byphunsanit

สร้าง Data Dictionary แค่คลิก

สร้าง Data Dictionary ใน sql server แบบง่ายๆ เพียงแค่คลิกเท่านั้น บทความนี้เป็นเวอร์ชั้นปรับปรุงของ สร้าง Data Dictionary แบบด่วนๆ ให้ทำได้ง่ายขั้น

  1. เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
  2. copy
    SET ANSI_PADDING  OFF;
    GO
    
    SET NOCOUNT ON;
    GO
    
    SET TEXTSIZE 8192;
    GO
    
    PRINT '<!doctype html>';
    PRINT '<html lang="th">';
    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>';
    
    DECLARE @record CURSOR;
    DECLARE @table VARCHAR(128);
    DECLARE @object_id INT;
    DECLARE @schema_id INT;
    
    SET @record = CURSOR
    FOR
    SELECT [name] AS [table], [object_id], [schema_id]
    FROM sys.tables AS t
    WHERE type_desc = 'USER_TABLE'
    
    OPEN @record
    
    FETCH NEXT
    FROM @record
    INTO @table, @object_id, @schema_id
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT CAST('<br><table><caption><b>' + @table + '</b>' + ISNULL(' : '+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</caption>' AS NTEXT)
            ,CAST('<thead><tr>' + '<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)
            ,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>' + '</tr></thead><tbody>' AS NTEXT)
        FROM sys.schemas AS s
        -- get description of table, if available
        LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = @object_id
            AND ep.minor_id = 0
            AND ep.NAME = 'MS_Description'
            AND ep.class = 1
        WHERE s.[schema_id] = @schema_id
        AND NOT EXISTS (
                SELECT *
                FROM sys.extended_properties ms
                WHERE ms.major_id = @object_id
                    AND ms.minor_id = 0
                    AND ms.class = 1
                    AND ms.[name] = 'microsoft_database_tools_support'
                )
    
        UNION ALL
    
      SELECT '' 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 AS c
        INNER JOIN sys.schemas s ON s.[schema_id] = @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 = @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 = @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] = @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 = @object_id
            AND fk.parent_column_id = c.column_id
        WHERE c.object_id = @object_id
        AND NOT EXISTS (
                SELECT *
                FROM sys.extended_properties ms
                WHERE ms.major_id = @object_id
                    AND ms.minor_id = 0
                    AND ms.class = 1
                    AND ms.[name] = 'microsoft_database_tools_support'
                )
    
        UNION ALL
    
        SELECT '' AS html
            ,'' AS html1
            ,'</tbody></table><br><hr><br>' AS html2
    
    
           FETCH NEXT
           FROM @record
           INTO @table, @object_id, @schema_id
    END
    
    CLOSE @record
    
    DEALLOCATE @record
    
    PRINT '<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit (Phunsanit@hotmail.com)</a>';
    PRINT '</body>';
    PRINT '</html>';
    
  3. คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
  4. เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

ตัวอย่างผลงานที่เร็วกว่ามาม่า

test

create date : 2016/07/14

—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-

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
)


PlusMagi.com by Pitt Phunsanit (Phunsanit@hotmail.com)
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

เวลานำไปให้ให้เครดิตผมด้วยนะครับ ถ้าหากเจอข้อบกพร่องแจ้งผมด้วยนะครับ จะได้ปรับปรุงให้คนอื่นใช้กันต่อไป “สังคมแห่งการแบ่งปันครับ”

Byphunsanit

สร้าง 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 ตัวนี้มีอะไรพิเศษมากกว่าตัวอื่นๆ หลายอย่าง คือ

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

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

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)

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