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