Is it possible to get a list of tables which don't have a specific extended property?

First things first: I am using Microsoft SQL Server Management Studio (if you need more information, just tell me).

Here is my problem: I have a really high number of tables and I am trying to bring some structure to it.

My team decided to 'just' add certain extended properties to each table:

Name
Date
Link to Doku

Each table should have at these 3 ext. properties. And of course there should be an info at the value of each of them.

Now I am trying to use a script to get all the tables which do NOT have those extended properties.

So the result of the script should be:

A list of all tables which

missing one or more of the "must have" extended properties
missing the value of one or more of the "must have" extended properties

At the end a result table would be nice - something like

TableName	Problem
Table_A	Missing ext. property
Table_B	missing value

I tried things like

select t.name as tablename, p.* 
from sys.tables t
left join sys.extended_properties p on t.object_id = p.major_id

to get a list of all tables and ext. properties.

But I have no clue to get the list I was talking about at the beginning.

Any ideas?

Chris

Solution

This query will return a row for each TABLE in the database, with columns reporting the presence (or absence) of those named extended-properties, as well as an indication if the extended-properties exist but have an empty @value = N'' string.

DBFiddle example: https://dbfiddle.uk/r3lLCcR1

When...
- Table_A has EPs for 'Name', 'Date', and 'Link'.
- Table_B has EPs for 'Name' and 'Date', but not 'Link'. And the 'Date' EP is an empty-string.
- Table_C has no EPs.

...the result:

`TableSchema`	`TableName`	`HasName`	`HasDate`	`HasLink`	`HasNonemptyName`	`HasNonemptyDate`	`HasNonemptyLink`
`dbo`	`Table_A`	true	true	true	true	true	true
`dbo`	`Table_B`	true	false	true	false	false	true
`dbo`	`Table_C`	false	false	false	false	false	false

WITH eps AS (

    SELECT
        ep.major_id AS "object_id",
        ep."name"   AS epName,
        CONVERT( nvarchar(4000), ep."value" ) AS epValue
    FROM
        sys.extended_properties AS ep
    WHERE
        ep.class = 1
        AND
        ep.minor_id = 0
        AND
        ep."name" IN ( N'Name', N'Date', N'Link' )
        AND
        SQL_VARIANT_PROPERTY( ep."value", 'BaseType' ) = 'nvarchar'
),
j AS (
  
    SELECT
        OBJECT_SCHEMA_NAME( t."object_id" ) AS TableSchema,
        t."name" AS TableName,
        e.epName,
        e.epValue
    FROM
        sys.tables AS t
        LEFT OUTER JOIN eps AS e ON t."object_id" = e."object_id"
),
epCols AS (

    SELECT
        TableSchema,
        TableName,
        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Name' THEN 1 END ) ) AS HasName,
        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Date' THEN 1 END ) ) AS HasDate,
        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Link' THEN 1 END ) ) AS HasLink,

        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Name' AND LEN( j.epValue ) > 0 THEN 1 END ) ) AS HasNonemptyName,
        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Date' AND LEN( j.epValue ) > 0 THEN 1 END ) ) AS HasNonemptyDate,
        CONVERT( bit, COUNT( CASE WHEN j.epName = N'Link' AND LEN( j.epValue ) > 0 THEN 1 END ) ) AS HasNonemptyLink
    FROM
        j
    GROUP BY
        TableSchema,
        TableName
)
SELECT
    TableSchema,
    TableName,
    
    HasName,
    HasDate,
    HasLink,
    HasNonemptyName,
    HasNonemptyDate,
    HasNonemptyLink

FROM
    epCols

ORDER BY
    TableSchema,
    TableName