Documenting Databases with Extended Properties - value is being overwritten 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 &gt; 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?
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 &gt; 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 &lt;DatbaseName&gt; 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 &gt; 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 &gt; 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