สร้าง 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
—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-
| 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 |
—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-
| 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])
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ
เวลานำไปให้ให้เครดิตผมด้วยนะครับ ถ้าหากเจอข้อบกพร่องแจ้งผมด้วยนะครับ จะได้ปรับปรุงให้คนอื่นใช้กันต่อไป “สังคมแห่งการแบ่งปันครับ”
About the author