Is it possible to query a null extended property in SQL Server?

Question

I am trying to come up with a way to query a result set that returns the schema/table/column for any column that doesn't have an extended property associated with it so my team can understand where to add documentation.

That being said...is it possible to query an extended property that doesn't have a value? I've tried the following as well as switching out the last line with p.value = '' but no cigar.

SELECT
    SCHEMA_NAME(tbl.schema_id) AS SchemaName, 
    tbl.name AS TableName, 
    clmns.name AS ColumnName
FROM 
    sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id 
WHERE SCHEMA_NAME(tbl.schema_id) = 'schema_name'
   AND tbl.name = 'table_name'
   AND p.value is null

`WHERE NOT EXISTS (SELECT 1 FROM sys.extended_properties ...` works? — Charlieface, Mar 03 '21 at 18:16
"Having no value" is not the same thing as "not existing". Your query will work for an extended property with value `NULL`, which is not the same thing as the property not being present. Your title seems to ask for one thing and the question body another. — Jeroen Mostert, Mar 03 '21 at 18:33

score 4 · Accepted Answer · answered Mar 03 '21 at 18:17

Try this:

SELECT
    s.[name] AS [schema_name]
    , t.[name] AS [table_name]
    , c.[name] AS [column_name]
    , x.[value] AS [MS_Description]
FROM [sys].[schemas] AS s
INNER JOIN [sys].[tables] AS t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN [sys].[columns] AS c
    ON t.[object_id] = c.[object_id]
LEFT OUTER JOIN [sys].[extended_properties] AS x
    ON t.[object_id] = x.[major_id]
    AND c.[column_id] = x.[minor_id]
    AND x.[name] = 'MS_Description'
WHERE
    t.[name] = 'your_table_name'
    AND s.[name] = 'your_schema_name';

Sample resultset:

+-------------+------------+----------------+-------------------------+
| schema_name | table_name |  column_name   |     MS_Description      |
+-------------+------------+----------------+-------------------------+
| dbo         | tblClients | pk_ClientID    | NULL                    |
| dbo         | tblClients | ClientName     | Client's business name. |
| dbo         | tblClients | PrimaryContact | NULL                    |
| dbo         | tblClients | Addr           | NULL                    |
| dbo         | tblClients | Addr2          | NULL                    |
| dbo         | tblClients | Addr3          | NULL                    |
| dbo         | tblClients | City           | NULL                    |
| dbo         | tblClients | State          | NULL                    |
| dbo         | tblClients | Zipcode        | NULL                    |
| dbo         | tblClients | Phone          | NULL                    |
+-------------+------------+----------------+-------------------------+

Is it possible to query a null extended property in SQL Server?

1 Answers1