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:
> 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
Last activity: Dec 4, 2019, 09:52 AM