SQL Server – Documenting Databases with Extended Properties Overwritten Values

sql serversql-server-2017

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:

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:

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?

Best Answer

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:

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

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

Related Solutions

Sql-server – Different Results From sys.databases As Different Users

This appears to be a matter of access rights and the specific user in the database. The first thing to understand is that sys.databases in any database is simply a view that exposes parts of the system table that lives in resourcedb. Anytime you are querying that in any database, you're hitting the same table but through a different view.

The second thing to understand is each database has a dbo user. When you use the execute as user = N'dbo', that means you're dbo in the context of that specific database. In your specific example, the dbo user in master and the dbo user in tempdb are two different security principals.

What you're seeing is simply a matter of what each principal is allowed to see. dbo users, being the owner login of each specific database, only have rights to see the following databases:

master (in order to look up control information)
tempdb (in order to create temporary objects when necessary)
The database they own

Note that the dbo user in master, since that database needs to see and supply control information for all databases, has rights to view all databases on the server.

Sql-server – Efficiently query MAX over multiple ranges

Here's a solution using CROSS APPLY, which does the same TOP query for each customer_id:

SELECT MAX(b.MaxQuantity) AS quantity
  FROM
  (
    SELECT 1 AS customer_id UNION ALL
    SELECT 2
  ) a
  CROSS APPLY
  (
    SELECT TOP 1
      quantity AS MaxQuantity
      FROM orders o
      WHERE o.customer_id = a.customer_id
      ORDER BY quantity DESC
  ) b;

This does the same work as the UNION ALL-based query you wrote in the Fiddle; the difference is that the customer_id input is abstracted from the meat of the query, so it can easily be converted to use a table variable or table-valued parameter, which will result in a static query plan, which is important. This approach will work well for a small number of customer_id values, and simply removing the outer MAX will return the maximum for each customer. I don't believe there's a way to further optimize this query for a small number of customer_ids using these data structures (assuming the customer_ids are random, and not a range).

For a large number of customer_ids, it probably is cheaper to do the index scan and stream aggregate than many seeks. To get this going faster, you'd have to move to some kind of denormalized data structure. MAX isn't supported in an indexed view, so rolling your own mechanism is the only way to go, either in application logic or triggers. Depending on the read/write ratio on this table, that may or may not be faster than the above approach: you'd have to test it in your exact scenario.

Best Answer

Related Solutions

Sql-server – Different Results From sys.databases As Different Users

Sql-server – Efficiently query MAX over multiple ranges

Related Question