sql-servert-sqlsystem-tables

List Tables and Table Extended Properties


I want to return a list of all tables within my database along with any related extended properties for the table.

This gives me the list of tables:

    SELECT TABLE_NAME AS tTableName, TABLE_TYPE AS tTableType
    FROM information_schema.tables AS t
    WHERE TABLE_TYPE = N'BASE TABLE'
    ORDER BY TABLE_NAME

This gives me the list of Extended Properties for Tables that have them:

    SELECT OBJECT_NAME(ep.major_id) AS [epTableName],
    CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]
    FROM sys.extended_properties AS ep
    WHERE ep.name = N'MS_Description' AND ep.minor_id = 0

I cannot see how to combine these and preserve the WHERE conditions correctly. THis is wrong but it will give you insight as to where I went wrong:

    SELECT TABLE_NAME AS tTableName, TABLE_TYPE AS tTableType,
    OBJECT_NAME(ep.major_id) AS [epTableName],
    CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]
    FROM information_schema.tables AS t
    LEFT OUTER JOIN sys.extended_properties AS ep ON
    t.table_name= OBJECT_NAME(ep.major_id) 
    WHERE TABLE_TYPE = N'BASE TABLE'
    -- No clue where to put this: AND ep.name = N'MS_Description' AND ep.minor_id = 0
    ORDER BY TABLE_NAME

Added from Joe's comment below Simon1979's answer...

SELECT 
    t.Table_Name, 
    q.epExtendedProperty 
FROM 
    information_schema.tables t 
    LEFT OUTER JOIN 
   (SELECT 
        OBJECT_NAME(ep.major_id) epTableName, 
        CAST(ep.Value AS nvarchar(500)) AS epExtendedProperty 
    FROM 
        sys.extended_properties ep 
    WHERE 
        ep.name = N'MS_Description' AND 
        ep.minor_id = 0) q 
        ON  t.Table_Name = q.epTableName 
WHERE 
    t.Table_Type = N'BASE TABLE' AND 
    t.Table_Name <> 'sysdiagrams' 
ORDER BY 
    q.epExtendedProperty ASC, 
    t.Table_Name ASC 

Solution

  • I think the below achieves what you want, though I don't have any extended properties on my tables that fit the criteria so it's hard to check if it works:

    SELECT TABLE_NAME AS tTableName, 
        TABLE_TYPE AS tTableType,
        q.epTableName,
        q.epExtendedProperty
    FROM information_schema.tables AS t
        LEFT OUTER JOIN (SELECT OBJECT_NAME(ep.major_id) AS [epTableName],
            CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]
            FROM sys.extended_properties ep
            WHERE ep.name = N'MS_Description' AND ep.minor_id = 0) As q
        ON t.table_name = q.epTableName 
    WHERE TABLE_TYPE = N'BASE TABLE'
    ORDER BY TABLE_NAME