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