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

Question

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

Answer 1

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