1

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.

0

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;
  • any reason you're preferring the manual cursor over sp_msforeachdb here? – Peter Vandivier Dec 4 '19 at 9:55
  • @Randi Vertongen this was one of the first script i used. There are some inconstancy in the rendering hence why i am not using it. In term of TSQL script its close to what am after. I can use your above script by selecting each server individually which works . – Daniel Dec 4 '19 at 9:59
  • @PeterVandivier Personal preference. Default sp_msforeachdb has its limitations. See here – Randi Vertongen Dec 4 '19 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.