sql-server - 是否可以在 SQL Server 中查询空扩展属性？

Question

我试图想出一种方法来查询一个结果集，该结果集为没有与之关联的扩展属性的任何列返回架构/表/列，以便我的团队可以了解在哪里添加文档。

话虽这么说...是否可以查询没有值的扩展属性？我尝试了以下方法，并用p.value = ''但没有雪茄来切换最后一行。

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

标签： sql-serversql-server-2017extended-properties

Answer 1

尝试这个：

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

样本结果集：

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