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
CodePudding user response:
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