CODEWITHSUNDEEP
sqlsql-servert-sqldata-dictionaryextended-properties
suhdude
suhdude

Reputation: 3

Get metadata from extended properties (SQL Server)

I am building a data dictionary and trying to get some info from the extended properties.

I have a script to add extended properties (working as expected).

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'This column stores the bla bla bla.' ,
@level0type=N'SCHEMA', 
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE', 
@level1name=N'my_super_crazy_table',--Table Name 
@level2type=N'COLUMN', 
@level2name=N'my_super_crazy_column'--Column Name
GO

Now I want to ideally be able to see when these are created/updated (bonus if I can see what user -- suser_sname())

My final query for the data dictionary is below:

SELECT tbl.name as [table_name],
       clmns.name as [column_name],
       exprop.value as [column_description]
       -- exprop.*
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id AND exprop.minor_id = clmns.column_id
WHERE (tbl.name IN ('my_super_crazy_table') and exprop.class = 1)

Output:

     table_name     |     column_name     |       column_description
my_super_crazy_table|my_super_crazy_column|This column stores the bla bla bla.

Is there any other sys table that could give me the information I'm looking for? Thanks

Upvotes: 0

Views: 710

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

SQL Server doesn't currently store any information about when extended properties are added or updated, or by who.

There's quite a bit more logic that you'd probably eventually want to implement. Here's my current best script for it.

https://www.csvreader.com/posts/data_dictionary.sql

SELECT
    d.[primary key],
    d.[foreign key],
    CASE
        WHEN LEN(d.[column]) = 0 THEN d.[table]
        ELSE ''
    END AS [table],
    d.[column],
    CAST(d.[description] AS VARCHAR(MAX)) AS [description],
    d.[data type],
    d.nullable,
    d.[identity],
    d.[default]
FROM
    (
        SELECT
            '' AS [primary key],
            '' AS [foreign key],
            s.[name] AS [schema],
            CASE
                WHEN s.[name] = 'dbo' THEN t.[name]
                ELSE s.[name] + '.' + t.[name]
            END AS [table],
            '' AS [column],
            ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') AS [description],
            '' AS [data type],
            '' AS nullable,
            '' AS [identity],
            '' AS [default],
            NULL AS column_id
        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
            CASE
                WHEN pk.column_id IS NOT NULL THEN 'PK'
                ELSE ''
            END AS [primary key],
            CASE
                WHEN fk.primary_table IS NOT NULL
                    THEN fk.primary_table + '.' + fk.primary_column
                ELSE ''
            END AS [foreign key],
            s.[name] AS [schema],
            CASE
                WHEN s.[name] = 'dbo' THEN t.[name]
                ELSE s.[name] + '.' + t.[name]
            END AS [table],
            c.[name] AS [column],
            ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') AS [description],
            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 AS [data type],
            CASE
                WHEN c.is_nullable = 1 THEN 'Y'
                ELSE ''
            END AS nullable,
            CASE
                WHEN c.is_identity = 1 THEN 'Y'
                ELSE ''
            END AS [identity],
            ISNULL(dc.[definition], '') AS [default],
            c.column_id
        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'
            )
    ) d
ORDER BY
    d.[schema],
    d.[table],
    d.column_id;

Upvotes: 1

Related Questions