Skip to content

Instantly share code, notes, and snippets.

@kevinmartintech

kevinmartintech/FindTableSchemaForDataDictionary

Last active March 7, 2025 06:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kevinmartintech/46b433b62d1d99933ca90eb8674e3046 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kevinmartintech/46b433b62d1d99933ca90eb8674e3046 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
FindTableSchemaForDataDictionary
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH TableInformation
AS (
SELECT
object_id = T.object_id
,[Schema Name] = S.name
,[Table Name] = T.name
,[Table Description] = CONVERT(nvarchar(MAX), EP.value)
FROM
sys.tables AS T
INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
LEFT OUTER JOIN sys.extended_properties AS EP ON EP.major_id = T.object_id
AND EP.minor_id = 0
AND EP.name = N'MS_Description'
WHERE
T.type = N'U'
AND T.is_ms_shipped = 0
AND T.name NOT IN (N'sysdiagrams')
)
,ForeignKeyInformation AS (
SELECT
sub.object_id,
sub.column_id,
[Referenced Schema.Table.Column List] = STRING_AGG(sub.[Referenced Column], N', '),
[Foreign Key List] = STRING_AGG(sub.[Foreign Key Name], N', '),
[Foreign Key Description List] = STRING_AGG(sub.[Foreign Key Description], N', '),
[Foreign Key Trust] = STRING_AGG(sub.[Foreign Key Trust], N', ')
FROM (
SELECT DISTINCT
C1.object_id,
C1.column_id,
[Referenced Column] = S2.name + N'.' + T2.name + N'.' + C2.name,
[Foreign Key Name] = O.name,
[Foreign Key Description] = CONVERT(nvarchar(MAX), EP.value),
[Foreign Key Trust] = IIF(fk.is_not_trusted = 0, N'Trusted', N'Untrusted')
FROM sys.foreign_key_columns AS FKC
INNER JOIN sys.foreign_keys AS fk ON FKC.parent_object_id = fk.parent_object_id
INNER JOIN sys.objects AS O ON O.object_id = FKC.constraint_object_id
LEFT OUTER JOIN sys.extended_properties AS EP
ON O.object_id = EP.major_id AND EP.minor_id = 0 AND EP.name = N'MS_Description'
INNER JOIN sys.tables AS T1 ON T1.object_id = FKC.parent_object_id
INNER JOIN sys.schemas AS S1 ON T1.schema_id = S1.schema_id
INNER JOIN sys.columns AS C1 ON C1.column_id = FKC.parent_column_id AND C1.object_id = T1.object_id
INNER JOIN sys.tables AS T2 ON T2.object_id = FKC.referenced_object_id
INNER JOIN sys.schemas AS S2 ON T2.schema_id = S2.schema_id
INNER JOIN sys.columns AS C2 ON C2.column_id = FKC.referenced_column_id AND C2.object_id = T2.object_id
) sub
GROUP BY sub.object_id, sub.column_id
)
,CheckConstraintInformation
AS (
SELECT
C.object_id
,C.column_id
,[Check Constraint List] = STRING_AGG(CC.name, N', ')
,[Check Constraint Definition List] = STRING_AGG(CC.definition, N', ')
,[Check Constraint Description List] = STRING_AGG(CONVERT(nvarchar(MAX), EP.value), N', ')
FROM
sys.check_constraints AS CC
LEFT OUTER JOIN sys.extended_properties AS EP ON CC.object_id = EP.major_id
AND EP.minor_id = 0
AND EP.name = N'MS_Description'
INNER JOIN sys.columns AS C ON CC.parent_object_id = C.object_id
AND CC.parent_column_id = C.column_id
INNER JOIN sys.tables AS T ON CC.parent_object_id = T.object_id
GROUP BY
C.object_id
,C.column_id
)
SELECT
[Schema Name] = TD.[Schema Name]
,[Table Name] = TB.name
,[Column Name] = C.name
,[Is Primary Key] = IIF(C.is_identity = 1, N'TRUE', N'FALSE')
,[Data Type] = TY.name + CASE WHEN TY.name IN (N'varchar', N'char', N'nvarchar', N'nchar', N'varbinary', N'binary')
THEN N'(' + CASE C.max_length
WHEN -1
THEN N'MAX'
ELSE CONVERT(varchar(50), IIF(TY.name IN (N'nvarchar', N'nchar'), C.max_length / 2, C.max_length))
END + N')'
WHEN TY.name IN (N'datetime2', N'datetimeoffset', N'time')
THEN N'(' + CONVERT(varchar(50), C.scale) + N')'
WHEN TY.name IN (N'decimal', N'numeric')
THEN N'(' + CONVERT(varchar(50), C.precision) + N',' + CONVERT(varchar(50), C.scale) + N')'
ELSE N''
END
,[Allow Null] = IIF(C.is_nullable = 1, N'TRUE', N'FALSE')
,[Is Identity] = IIF(C.is_identity = 1, N'TRUE', N'FALSE')
,[Is Masked] = IIF(C.is_masked = 1, N'TRUE', N'FALSE')
,[Is Computed] = IIF(C.is_computed = 1, N'TRUE', N'FALSE')
,[Default Value] = ISNULL(DC.definition, N'')
,[Column Description] = COALESCE(CONVERT(nvarchar(MAX), EP.value), N'')
,[Table Description] = ISNULL(TD.[Table Description], N'')
,[Referenced Schema.Table.Column List] = ISNULL(FKI.[Referenced Schema.Table.Column List], N'')
,[Foreign Key List] = ISNULL(FKI.[Foreign Key List], N'')
,[Foreign Key Description List] = ISNULL(FKI.[Foreign Key Description List], N'')
,[Foreign Key Trust] = ISNULL(FKI.[Foreign Key Trust], N'')
,[Check Constraint List] = ISNULL(CCI.[Check Constraint List], N'')
,[Check Constraint Description List] = ISNULL(CCI.[Check Constraint Description List], N'')
,[Check Constraint Definition List] = ISNULL(CCI.[Check Constraint Definition List], N'')
FROM
sys.tables AS TB
LEFT OUTER JOIN TableInformation AS TD ON TB.object_id = TD.object_id
LEFT OUTER JOIN sys.columns AS C ON C.object_id = TB.object_id
LEFT OUTER JOIN ForeignKeyInformation AS FKI ON C.object_id = FKI.object_id
AND C.column_id = FKI.column_id
LEFT OUTER JOIN CheckConstraintInformation AS CCI ON C.object_id = CCI.object_id
AND C.column_id = CCI.column_id
INNER JOIN sys.types AS TY ON C.system_type_id = TY.system_type_id
AND C.user_type_id = TY.user_type_id
LEFT OUTER JOIN sys.default_constraints AS DC ON DC.parent_object_id = TB.object_id
AND DC.parent_column_id = C.column_id
LEFT OUTER JOIN sys.extended_properties AS EP ON TB.object_id = EP.major_id
AND EP.minor_id = C.column_id
AND EP.name = N'MS_Description'
AND EP.class = 1
WHERE
TB.name NOT IN (N'sysdiagrams')
ORDER BY
TD.[Schema Name]
,TD.[Table Name]
OPTION (RECOMPILE);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment