1

This is a question about logic that could help me understand more how to iterate into my DBs.

On my server I have several AdventureWorks* databases:

enter image description here

I have a query that cold help me iterate through all my databases, AdventureWorks* and not:

CREATE TABLE #LIst_DB (name nvarchar(128))

INSERT INTO #LIst_DB 

select name from  sys.databases
WHERE database_id > 4
AND state = 0;

select * from #LIst_DB

DROP TABLE #LIst_DB

For each AdventureWorks* database I want to list AWBuildVersion next to each DB:

enter image description here

At this point I'm puzzled because there are 2 problems I don't know how to solve:

  1. How to run a select [Database Version] from AWBuildVersion for each database?
  2. What to do if a database has no AWBuildVersion table?

Here I found this query:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((
            SELECT '
UNION
SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name
FROM ' + quotename(NAME) + '.sys.tables WHERE NAME =  @TableName '
            FROM sys.databases
            ORDER BY NAME
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL
    ,N'@TableName varchar(30)'
    ,@TableName = 'AWBuildVersion'

That brings me close to my goal but it shows me the table name AWBuildVersion while I need the column Database Version = 11.0.2100.60

enter image description here

4

Comments inline:

-- The table to find
DECLARE @find nvarchar(257) = N'dbo.AWBuildVersion';

-- Holds results
DECLARE @results table 
(
    [Db_Name] sysname PRIMARY KEY,
    [Database Version] nvarchar(25) NOT NULL
);

-- Current database   
DECLARE @db sysname;

-- AdventureWorks databases cursor
DECLARE dbs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT D.[name] 
FROM sys.databases AS D
WHERE 
    D.[name] LIKE N'AdventureWorks%'
    AND D.state_desc = N'ONLINE'
    AND DATABASEPROPERTYEX(D.[name], 'Collation') IS NOT NULL
ORDER BY
    D.[name];

OPEN dbs;

WHILE 1 = 1
BEGIN
    -- Next database
    FETCH dbs INTO @db;
    IF @@FETCH_STATUS = -1 BREAK;

    -- Find [Database Version] if @find table exists
    INSERT @results ([Db_Name], [Database Version])
    EXECUTE sys.sp_executesql
        N'
            -- Construct 3-part object name
            DECLARE @object nvarchar(386) = 
                QUOTENAME(@db) + N''.'' +
                ISNULL(QUOTENAME(PARSENAME(@find, 2)), '''') + N''.'' +
                QUOTENAME(PARSENAME(@find, 1));

            -- Query to find [Database Version]
            DECLARE @sql nvarchar(max) = N''
                SELECT 
                    Db_Name = '''''' + @db + '''''',
                    [Database Version] 
                FROM '' + @object;

            IF OBJECT_ID(@object, N''U'') IS NOT NULL
                EXECUTE (@sql)',
        N'@db sysname, @find nvarchar(257)',
        @db = @db, @find = @find;
END;

CLOSE dbs; DEALLOCATE dbs;

SELECT
    R.[Db_Name],
    [Table_Name] = @find,
    R.[Database Version]
FROM @results AS R
ORDER BY
    R.[Db_Name];
4

I would not want to do that with T-SQL because it limits me in the amount of servers I could get the results from.

Instead I would create a PowerShell script like this:

$servers = @( 'server1', 'server2')

$query = "SELECT [Database Version] AS DatabaseVersion FROM [dbo].[AWBuildVersion];"

$results = @()
$comment = @()

# Loop through the servers
foreach ($server in $servers) {
    $databases = Get-DbaDatabase -SqlInstance $server | Where-Object {$_.Name -like 'AdventureWorks*'}
    Write-Host "Going through server $server"

    # Loop through the databases
    foreach ($database in $databases) {
        # Reset variables
        $result = @()
        $data = $null
        $comment = $null

        if ($database.Tables.Name -contains 'AWBuildVersion') {
            Write-Host "- Querying database $database"

            $data = Invoke-DbaSqlQuery -SqlInstance $server -Database $database.Name -Query $query 
        }
        else {
            $comment += 'Could not find table AWBuildVersion'
        }

        $result = [PSCustomObject]@{
            Server          = $server
            Database        = $database.Name
            DatabaseVersion = $data.DatabaseVersion
            Comment         = $comment -join ', '
        }

        $results += $result
    }
}

$results

result of the script

You do need the dbatools powershell module to run it.

What it does is:

  • Iterates through the servers you assign.
  • Retrieves the databases that have 'AdventureWorks' in the name.
  • Iterates through the databases and executes the query.
  • If it cannot find the table it adds a comment to the results that the table could not be found.
  • Thank you, it worked but the results are not formatted like in your picture. Do you know how I can format the result in the same 3 columns |Server|Database|DatabaseVersion| – Francesco Mantovani Mar 15 '18 at 8:25
  • I'm not sure why you would get a different result. I executed the script on my machines and was able to get the same result as in the picture. I did remove a line of code that generated an error. Can you send me a printscreen of what the result is on your machine? – SQLStad Mar 16 '18 at 7:16
  • they get formatted differently if you point it against just one server. Interesting. The code is brilliant and it works, I still have a few problem connecting to a remote server because I'm not administrator but I think I can sort it out – Francesco Mantovani Mar 17 '18 at 19:22
  • Connecting to local-host is fine, connecting to remote servers is still a pain WARNING: [Invoke-DbaSqlQuery][17:43:26] Failure | Exception calling "Open" with "0" argument(s): "Login failed for user..... I added -SqlCredential (Get-Credential sa) so it prompts me the login, i enter the password but then it gives me again the same error because is trying to login with MYDOMAIN\username instead of sa\password – Francesco Mantovani Mar 22 '18 at 4:57
2

I'd probably use sp_foreachDB contained in Brent Ozar's First Responder Kit:

EXEC dbo.sp_foreachDB @command1 = 'select [Database Version] from dbo.AWBuildVersion', 
                 @name_pattern = 'AdventureWorks%'

Haven't tested this but I believe the syntax should work.

  • tested, is not working: Msg 208, Level 16, State 1, Line 1 Invalid object name 'AWBuildVersion'. – Francesco Mantovani Mar 12 '18 at 21:14
  • I don't have all those AW DBs handy right now..this is some troubleshooting I believe you can take on yourself. – LowlyDBA Mar 13 '18 at 13:05

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.