Sql-server – How to list Extended Properties for all databases

central-management-serverpowershellsql server

I am currently working on task where we use Central Management Server and Server Group for easy access to our SQL server boxes.

I am trying to query the extended properties for all databases on multiple SQL Servers as can be seen below:

enter image description here

Is there a way that can be done using TSQL script?

I am using the code snippet from this social.msdn post that is pretty close to what I want to achieve, but my powershell skills are zero to none: Please find the script below :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "computer\instance" 

$dbs = $s.Databases

$query = @"
SELECT objtype, 
        objname, 
        name, 
        value 
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
"@

foreach($db in $dbs) {
    $ds = $db.ExecuteWithResults($query)

    $table = $ds.Tables[0];

    foreach($row in $table) {

        Write-Host $row.Item("objtype") `
            $row.Item("objname") `
            $row.Item("name") `
            $row.Item("value");
    }
}

To add to the complication I am using the above Powershell script within in Jenkins.

Best Answer

You could run the command in a cursor over the databases:

DECLARE @DbName nvarchar(500), @SQL nvarchar(1000);
DECLARE C CURSOR LOCAL FAST_FORWARD FOR 
SELECT QUOTENAME(name) from sys.databases order by database_id;
OPEN C;
FETCH NEXT FROM C INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL =
    N'
    USE '+@DbName+'
    SELECT '''+@DbName+''' as databasename ,name, value 
    FROM fn_listextendedproperty(default, default, default, default, default, default, default)'
    EXEC SP_EXECUTESQL @SQL;

    FETCH NEXT FROM C INTO @DbName;
END
CLOSE C;
DEALLOCATE C;
Related Question