Byphunsanit

Data Dictionary, Database, manual, table, คู่มือ, ฐานข้อมูล, ดาต้าดิกชันนารี, ตาราง

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

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

เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query

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 ([email protected])</a>';
PRINT '</body>';
PRINT '</html>';

คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

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

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

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

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

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

test

create date : 2016/07/14

About the author

phunsanit administrator

Recent Posts

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

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

test

create date : 2016/07/14

About the author

phunsanit administrator

Related News

JavaScript: & css CDN

DataTables: Add row form JavaScript

SQL Server: recursive หาวันเสาร์ทุก ๆ วันจนถึงวันนี้

Recent Posts