List Extended Database properties for all databases

,

I am using database properties to add some useful information for each database.

Tired of exploring each database from the menu in SSMS to look for specific information,

I decided to make a script listing the extended properties for all my databases.

I am pleased to offer this to the community 🙂

CREATE PROC ListExtendedDBproperties
as

    CREATE TABLE #PropList
    (
        class_desc      varchar(50),
        DBname          varchar(100),
        PropertyName    varchar(100),
        value           sql_variant
    )


    DECLARE @dbname varchar(50)
    DECLARE @cmd nvarchar(1000)
    
    DECLARE c1 CURSOR FORWARD_ONLY FOR
        SELECT name as DBname FROM sys.databases

    OPEN c1

    FETCH c1 INTO @dbname
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = 'INSERT INTO #PropList 
                                (class_desc, DBname, PropertyName, value) 
                    SELECT class_desc, ''' + @dbname + ''' as DBname, 
                       name as PropertyName, 
                       value 
                    FROM [' + @dbname + '].sys.extended_properties 
                    WHERE class = 0 ' 
        EXEC (@cmd)
        FETCH c1 INTO @dbname
    END

    CLOSE c1
    DEALLOCATE c1
    
SELECT * FROM #PropList ORDER BY DBname

Rate

Share

Share

Rate