1

I have a query that views some information about an entire instance of databases. Using Extended Properties, I have also added information on database level to what customer the database belongs to, among other things.

To view all the information collectively, my query looks like this:

SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.NAME = 'Shared' THEN value
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.NAME = 'Product' THEN value
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.NAME = 'Customer' THEN value
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d
               ON d.database_id = mf.database_id,
       sys.extended_properties SEP
WHERE  d.database_id > 4 -- Skip system databases 
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME 

And the results become this:

Results as not intended

As you can see, all Extended Properties "Shared", "Product", and "Customer" will be overwritten by the first database's Extended Properties. In reality, if the query worked as intended, it would be something like this instead:

Results as intended

Notice the emphasis on the last three columns. I have tried to group the query on SEP.name and SEP.value, but the result have not been to any help. How does one fix this?

  • Are you missing the join condition with sys.extended_properties? There's a comma before the previous join and no joining condition in the where, so you are doing the cartesian product for all extended properties. If you remove the aggregates and the group by clause, you will see that the rows are repeating for each extended property. – EzLo Mar 5 at 13:33
  • I believe you are right! However, what would I join sys.extended_properties with (assuming inner join)? – tobulos1 Mar 5 at 13:42
  • What version of SQL Server? – Aaron Bertrand Mar 5 at 14:10
  • We use SQL Server 2017 – tobulos1 Mar 6 at 6:28
2

Your query is missing the joining clause against sys.extended_properties. It's currently doing the cartesian product against the previous join result (notice the comma at the end of the first join and no condition on the WHERE linking the tables).

FROM
    sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id,
    sys.extended_properties SEP
WHERE
    d.database_id > 4 -- Skip system databases 
GROUP BY
    -- ...

I'd recommend always explicitly writing the JOIN operator (and avoid writing the relationships in the WHERE) since it makes the query more readable. If you want to apply cartesian just write CROSS JOIN.


Extended properties on database object are tricky because their record on sys.extended_properties have no id (they are listed as 0). The documentation says that database objects are listed with class = 0, so we are gonna query those:

enter image description here

enter image description here

The way you can retrieve these is through the system function sys.fn_listextendedproperty, by supplying all parameters as DEFAULT:

SELECT F.*  
FROM fn_listextendedproperty(default, default, default, default, default, default, default) AS F

enter image description here

However, this will only retrieve the extended properties of the database in which you are executing the function. So to see ADatabase's extended properties you will need to write ADatabase.sys.fn_listextendedproperty(...). This means that you will require a dynamic, database by database solution to correctly retrieve all these (in which case you can use the sys.extended_properties while preceding the corresponding USE <DatbaseName> before).


The following solution uses a temporary table and a cursor to retrieve all databases extended properties and finally join with your query by database_id. I've tested it and ran successfully (but always review it!).

IF OBJECT_ID('tempdb..#DatabaseExtendedProperties') IS NOT NULL
    DROP TABLE #DatabaseExtendedProperties

CREATE TABLE #DatabaseExtendedProperties (
    DatabaseID INT,
    DatabaseName SYSNAME,
    PropertyName SYSNAME,
    PropertyValue VARCHAR(MAX))

DECLARE 
    @DatabaseName NVARCHAR(100),
    @DatabaseID INT

DECLARE DatbaseCursor CURSOR FOR
    SELECT
        S.name,
        S.database_id
    FROM
        sys.databases AS S
    WHERE
        S.database_id > 4


OPEN DatbaseCursor
FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQL NVARCHAR(MAX) = '
        USE ' + QUOTENAME(@DatabaseName) + '

        INSERT INTO #DatabaseExtendedProperties (
            DatabaseID,
            DatabaseName,
            PropertyName,
            PropertyValue)
        SELECT
            DatabaseID = ' + CONVERT(NVARCHAR(10), @DatabaseID) + ',
            DatabaseName = ''' + QUOTENAME(@DatabaseName) + ''',
            PropertyName = T.name,
            PropertyValue = CONVERT(VARCHAR(MAX), T.value)
        FROM
            sys.extended_properties AS T
        WHERE
            T.class = 0'

    EXEC (@DynamicSQL)

    FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

END

CLOSE DatbaseCursor
DEALLOCATE DatbaseCursor

-- Your query with the temporary table's join
SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.PropertyName = 'Shared' THEN SEP.PropertyValue
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.PropertyName = 'Product' THEN SEP.PropertyValue
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.PropertyName = 'Customer' THEN SEP.PropertyValue
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d ON d.database_id = mf.database_id
       INNER JOIN #DatabaseExtendedProperties AS SEP ON SEP.DatabaseID = d.database_id
WHERE  d.database_id > 4 -- Skip system databases 
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME 
  • Wow, I never thought it would be this much work! Thank you so much for this! – tobulos1 Mar 6 at 6:25
1

You could also create a view that just concatenates all the extended_properties views from all the user databases. Here's how I would do it in SQL Server 2017:

DECLARE @view nvarchar(max) = N'CREATE VIEW dbo.all_extended_properties AS
  SELECT ';

SELECT @view += (SELECT STRING_AGG(N'DBID = ' 
  + CONVERT(varchar(11), database_id) 
  + ', DB = ''' + name + ''', * FROM ' 
  + QUOTENAME(name) + '.sys.extended_properties
  ',N' UNION ALL 
  SELECT ') FROM sys.databases WHERE database_id > 4
) + N';';

PRINT @view;
-- EXEC sys.sp_executesql @view;

On my system the output was:

CREATE VIEW dbo.all_extended_properties AS
  SELECT DBID = 5, DB = 'db1', * FROM [db1].sys.extended_properties
   UNION ALL 
  SELECT DBID = 6, DB = 'db2', * FROM [db2].sys.extended_properties
   UNION ALL 
  SELECT DBID = 7, DB = 'db3', * FROM [db3].sys.extended_properties
  ;

In older versions it's a little hackier (this is the first reason why I asked, and you should always tag your question with the version you need to support). Note that the STUFF() arguments will be very sensitive to minute formatting changes):

DECLARE @view nvarchar(max) = N'CREATE VIEW dbo.all_extended_properties AS
  SELECT ';

SELECT @view += STUFF((SELECT ' 
  UNION ALL 
  SELECT DBID = ' + CONVERT(varchar(11), database_id)
  +', DB = ''' + name + ''', * FROM ' 
  + QUOTENAME(name) + '.sys.extended_properties' 
  FROM sys.databases WHERE database_id > 4
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,26,'') + N'
  ;';

PRINT @view;
-- EXEC sys.sp_executesql @view;

Output was identical to the one above. In either case, your actual query doesn't need dynamic SQL or temp tables or loops/cursors; you can just join to this view instead (and you should never use , as your JOIN operator, even when you are specifying filters somewhere else):

...
FROM   sys.master_files mf
INNER JOIN sys.databases d
  ON d.database_id = mf.database_id
INNER JOIN dbo.all_extended_properties SEP
  ON d.database_id = SEP.DBID
  AND SEP.NAME IN (N'Customer', N'Product', N'Shared')
...

You just need to re-create the view any time you add or drop a database. You could do this in a server-level DDL trigger.

I don't think that your grouping should be necessary, you are only doing that for the SUM (and to hide your cross join explosion), which you can do with SUM(...) OVER (PARTITION BY...) on a modern enough version of SQL Server (the second reason I asked). But I didn't dig into the rest of the query - just focused on the underlying problem of "how do I get the extended properties from multiple databases?"

0

As sys.extended_properties only returns the extended properties in the context of the database, you will have to loop over the databases and get the corresponding extended properties.

Here is one way to do this:

CREATE TABLE #temp
    ([id] INT,
     [Database Name] NVARCHAR(255), 
     [Size (MB)] INT, 
     [State] NVARCHAR(20), 
     [Read Only] BIT, 
     [Created Date] DATETIME2,
     [Shared] NVARCHAR(255),
     [Product] NVARCHAR(255),
     [Customer] NVARCHAR(255));

DECLARE @sql NVARCHAR(max)
DECLARE c cursor for select name from sys.databases where database_id > 4 
DECLARE @dbname NVARCHAR(50)
OPEN c 
FETCH NEXT FROM c into @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
USE '+QUOTENAME(@dbname)+'

SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
    CONVERT(NVARCHAR(255),MAX(CASE
             WHEN SEP.NAME = ''Shared'' THEN value
           END))                         AS [Shared],
      CONVERT(NVARCHAR(255),MAX(CASE
             WHEN SEP.NAME = ''Product'' THEN value
           END))                          AS [Product],
       CONVERT(NVARCHAR(255),MAX(CASE
             WHEN SEP.NAME = ''Customer'' THEN value
           END))                  AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d
               ON d.database_id = mf.database_id,
       sys.extended_properties SEP
WHERE  QUOTENAME(d.name) = '''+QUOTENAME(@dbname)+'''

GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME; '
INSERT INTO #temp
EXEC SP_EXECUTESQL @sql;

FETCH NEXT FROM C INTO @dbname;
END
CLOSE c
DEALLOCATE c
SELECT * FROM #temp;

DROP TABLE #temp;

Test Result

id  Database Name   Size (MB)   State   Read Only   Created Date    Shared  Product Customer
7   Test    81  ONLINE  0   2016-04-04 09:29:28.1530000 True    AppB    CustB
28  my_test 324 ONLINE  0   2018-12-11 15:30:23.0270000 False   AppA    CustA

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.