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

  1. เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
  2. copy[code language=”sql” title=”generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit”]
    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>’;
    [/code]

  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)
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

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