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

Answer 1

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