By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table_A ( Foo int NOT NULL );
CREATE TABLE Table_B ( Foo int NOT NULL );
CREATE TABLE Table_C ( Foo int NOT NULL );
EXEC sp_addextendedproperty @name = N'Name', @value = N'name-value',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'Table_A';
EXEC sp_addextendedproperty @name = N'Date', @value = N'date-value',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'Table_A';
EXEC sp_addextendedproperty @name = N'Link', @value = N'link-value',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'Table_A';
EXEC sp_addextendedproperty @name = N'Name', @value = N'name-value',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'Table_B';
EXEC sp_addextendedproperty @name = N'Date', @value = N'', /* empty value :o */
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'Table_B';
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' )
),
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"
)
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
| TableSchema | TableName | HasName | HasDate | HasLink | HasNonemptyName | HasNonemptyDate | HasNonemptyLink |
|---|---|---|---|---|---|---|---|
| dbo | Table_A | True | True | True | True | True | True |
| dbo | Table_B | True | True | False | True | False | False |
| dbo | Table_C | False | False | False | False | False | False |
Warning: Null value is eliminated by an aggregate or other SET operation.