Byphunsanit

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

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

Updateอ่าน สร้าง Data Dictionary แค่คลิก แทนครับ

ต้องเขียน Data Dictionary ให้โครงการที่ทำอยู่ แต่มันมีหลายตารางมาก และที่สำคัญคือ แต่ละตารางจะมี relation ยุบยับเต็มไปหมด copy โครงสร้างมาแต่ละตารางมาวางในเอ็กส์เซล์ที่ละตัวก็ตาลาย พลาดได้ง่ายๆเลย

วิธีที่คิดออกคือ มันต้องมีโปรแกรมที่ช่วยงานนี้ได้ซิ เราไม่ใช่คนแรกที่ต้องเขียน Data Dictionary ซะหน่อย มีจริงๆ แต่แพง ไม่ก็ดูแปลกๆ เปลี่ยนไปใช้อีกวิธีคือ ทำไมไม่ให้ SQL ทำให้ละ search เจอ SQL Server Data Dictionary แต่มันก็ดูยาก เลยต้องเอามาเขียนใหม่ซะเอง

query ที่เขียนขึ้นมาใหม่คือ

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

SET ANSI_DEFAULTS OFF;
GO
 
SET NOCOUNT ON;
GO
 
SET TEXTSIZE 8192;
GO
 
PRINT '<HTML><body>'
PRINT '<!doctype html>'
PRINT '<html>'
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>'
 
SELECT html
    ,html1
    ,html2
FROM (
    SELECT s.[name] AS [schema]
        ,CASE
            WHEN s.[name] = 'dbo'
                THEN t.[name]
            ELSE s.[name] + '.' + t.[name]
            END AS [table]
        ,NULL AS column_id
        ,CAST('<table><cption><b>' + t.[name] + '</b>' + ISNULL(' : '+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</caption>' AS NTEXT) AS html
        ,CAST('<thead>' + '<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) AS html1
        ,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>' + '</thead><tbody>' AS NTEXT) AS html2
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
    -- get description of table, if available
    LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
        AND ep.minor_id = 0
        AND ep.NAME = 'MS_Description'
        AND ep.class = 1
    WHERE t.is_ms_shipped = 0
        AND NOT EXISTS (
            SELECT *
            FROM sys.extended_properties ms
            WHERE ms.major_id = t.[object_id]
                AND ms.minor_id = 0
                AND ms.class = 1
                AND ms.[name] = 'microsoft_database_tools_support'
            )
 
    UNION ALL
 
    SELECT s.[name] AS [schema]
        ,CASE
            WHEN s.[name] = 'dbo'
                THEN t.[name]
            ELSE s.[name] + '.' + t.[name]
            END AS [table]
        ,c.column_id
        ,'' 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 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]
    -- 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 = t.[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 = t.[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] = t.[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 = t.[object_id]
        AND fk.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0
        AND NOT EXISTS (
            SELECT *
            FROM sys.extended_properties ms
            WHERE ms.major_id = t.[object_id]
                AND ms.minor_id = 0
                AND ms.class = 1
                AND ms.[name] = 'microsoft_database_tools_support'
            )
 
    UNION ALL
 
    SELECT s.[name] AS [schema]
        ,CASE
            WHEN s.[name] = 'dbo'
                THEN t.[name]
            ELSE s.[name] + '.' + t.[name]
            END AS [table]
        ,999999 AS column_id
        ,'' AS html
        ,'' AS html1
        ,'</tbody></table><br><hr><br>' AS html2
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
    WHERE t.is_ms_shipped = 0
        AND NOT EXISTS (
            SELECT *
            FROM sys.extended_properties ms
            WHERE ms.major_id = t.[object_id]
                AND ms.minor_id = 0
                AND ms.class = 1
                AND ms.[name] = 'microsoft_database_tools_support'
            )
    ) tmp
ORDER BY tmp.[schema]
    ,tmp.[table]
    ,tmp.column_id;
 
PRINT '<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit (Phunsanit@hotmail.com)</a>'
PRINT '</body>'
PRINT '</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 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)

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

Tag Archive manual

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

DATABASE NAME

create date : 2016/05/30

Recent Posts