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
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
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