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 2147483647; 
GO

PRINT '<!doctype html>'
PRINT '<html>'
PRINT '<head>'
PRINT '<meta charset="utf-8">'
PRINT '<title>SQL Server Data Dictionary by Pitt Phunsanit</title>'
PRINT '<style>
    body { font-family: "Calibri", "Segoe UI", sans-serif; padding: 30px; line-height: 1.6; background-color: #f5f7fa; }
    h1 { color: #2f5496; border-bottom: 2px solid #2f5496; padding-bottom: 10px; }
    h3 { color: #555; }
    table { background: #fff; border-collapse: collapse; width: 100%; margin-bottom: 40px; box-shadow: 0 2px 5px rgba(0,0,0,0.1); page-break-inside: avoid; }
    table caption { text-align: left; padding: 15px; font-size: 16pt; color: #2f5496; font-weight: bold; background: #e9eff7; border: 1px solid #7ba0cd; border-bottom: none; }
    table thead tr { background-color: #4e80bc; color: white; text-align: center; }
    table th, table td { border: 1px solid #7ba0cd; padding: 10px; font-size: 10pt; }
    table tbody tr:nth-of-type(odd) { background: #f2f6fb; }
    table tbody tr:hover { background: #e2ebf5; }
    .pk { color: #d9534f; font-weight: bold; text-align: center; }
    .fk { color: #5bc0de; font-size: 9pt; }
    .footer { font-size: 10pt; color: #777; margin-top: 50px; border-top: 1px solid #ccc; padding-top: 20px; text-align: center; }
    a { color: #4e80bc; text-decoration: none; }
    a:hover { text-decoration: underline; }
</style>'
PRINT '</head>'
PRINT '<body>'
PRINT '<h1>Database: ' + DB_NAME() + '</h1>'
PRINT '<h3>Generated Date: ' + CONVERT(VARCHAR(10), GETDATE(), 111) + '</h3>'

SELECT
    html + html1 + html2 AS [GeneratedHTML]
FROM
(
    -- 1. Table Header & Caption (คง REPLACE ไว้เพราะ Description ตารางอาจมีอักขระพิเศษ)
    SELECT
        s.[name] AS [schema], 
        t.[name] AS [table], 
        0 AS column_id,
        CAST('<table><caption>Table: ' + s.[name] + '.' + t.[name] + ISNULL(' <small style="font-weight:normal; color:#666;">(' + REPLACE(REPLACE(CAST(ep.[value] AS NVARCHAR(MAX)), '<', '&lt;'), '>', '&gt;') + ')</small>', '') + '</caption>' AS NVARCHAR(MAX)) AS html,
        CAST('<thead><tr><th style="width:40px;">PK</th><th style="width:160px;">FK (Referenced Table)</th><th style="width:220px;">Column Name</th><th>Description</th>' AS NVARCHAR(MAX)) AS html1,
        CAST('<th style="width:140px;">Data Type</th><th style="width:50px;">Null</th><th style="width:50px;">ID</th><th style="width:180px;">Default Value</th></tr></thead><tbody>' AS NVARCHAR(MAX)) AS html2
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
    LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id] AND ep.minor_id = 0 AND ep.name = 'MS_Description'
    WHERE t.is_ms_shipped = 0

    UNION ALL

    -- 2. Table Rows (Columns)
    SELECT
        s.[name], t.[name], c.column_id,
        '',
        '<tr><td class="pk">' + CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END + '</td>' +
        '<td class="fk">' + ISNULL(fk.primary_table + '.' + fk.primary_column, '') + '</td>' +
        '<td><b>' + c.[name] + '</b></td>' +
        '<td>' + ISNULL(REPLACE(REPLACE(CAST(ep.[value] AS NVARCHAR(MAX)), '<', '&lt;'), '>', '&gt;'), '') + '</td>' AS html1,
        '<td>' + sty.[name] + 
        CASE
            WHEN sty.[name] IN ('char', 'nchar', 'varchar', 'nvarchar', 'binary', 'varbinary') 
            THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CAST(CASE WHEN sty.[name] IN ('nchar', 'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS VARCHAR(10)) END + ')'
            WHEN sty.[name] IN ('numeric', 'decimal') 
            THEN '(' + CAST(c.precision AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
            ELSE ''
        END + '</td>' +
        '<td style="text-align:center;">' + CASE WHEN c.is_nullable = 1 THEN 'Y' ELSE '' END + '</td>' +
        '<td style="text-align:center;">' + CASE WHEN c.is_identity = 1 THEN 'Y' ELSE '' END + '</td>' +
        '<td><small>' + ISNULL(dc.[definition], '') + '</small></td></tr>' AS html2 -- เอา REPLACE ออกที่นี่
    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]
    LEFT OUTER JOIN sys.types sty ON sty.user_type_id = c.user_type_id
    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'
    LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = t.[object_id] AND dc.parent_column_id = c.column_id
    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]
    LEFT OUTER JOIN (
        SELECT fkc.parent_object_id, fkc.parent_column_id, 
               OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS primary_schema,
               OBJECT_NAME(fk.referenced_object_id) AS primary_table, 
               COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS primary_column
        FROM sys.foreign_keys fk
        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
    ) fk ON fk.parent_object_id = t.[object_id] AND fk.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0

    UNION ALL

    -- 3. Table Footer
    SELECT
        s.[name], t.[name], 999999,
        '', '', '</tbody></table>'
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
    WHERE t.is_ms_shipped = 0
) tmp
ORDER BY tmp.[schema], tmp.[table], tmp.column_id;

PRINT '<div class="footer">'
PRINT 'Developed by <a href="https://pitt.plusmagi.com" target="_blank">PlusMagi.com by Pitt Phunsanit</a><br>'
PRINT 'Contact: [email protected] | Source: SQL Server Metadata'
PRINT '</div>'
PRINT '</body></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 KeyForeign keyColumn NameDescriptionData TypeAllow NullsIdentityDefault 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 KeyForeign keyColumn NameDescriptionData TypeAllow NullsIdentityDefault Value

PK	areas_cities.city_id	city_id	int
PK		language	char (2)
		name	nvarchar (600)

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

อ่านเพิ่มเติม

วัน: 5 มิถุนายน 2016

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

DATABASE NAME

create date : 2016/05/30