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)) , '<', '<') , '>', '>') + ') </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)) , '<', '<') , '>', '>') , '') + '</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) |
จริง ๆ มันมีสีสันด้วยนะ สวยจนเอาไปใช้ได้เลยไม่ต้องแต่งอะไรอีก
อ่านเพิ่มเติม