Sample Header Ad - 728x90

How to list Extended Properties for all databases

1 vote
1 answer
1714 views
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 :
$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;

    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.
Asked by Daniel (137 rep)
Dec 4, 2019, 09:31 AM
Last activity: Dec 4, 2019, 09:52 AM