Querying multiple databases on the same server

Question

I need to query all databases on a server. I cursor through all the databases and assign each next to a variable @DB

I can then use @DB by executing SQL from within the cursor and this is all fine until I need to do another if exists to handle databases which don't contain a status I need to check. Code below.

Thanks,

set nocount on
use [master]
declare @DB sysname
declare @sql nvarchar(max)
declare curDB cursor for
select [name] from sysdatabases
order by name


create table #results (
[Database] nvarchar(max),
[CompanyCode] nvarchar(max),
[Title] nvarchar(max),
[Status] nvarchar(max)
)

use [master]
open curDB
fetch next from curDB into @DB
while @@FETCH_STATUS=0
begin

set @sql = 'use [' + @DB + ']; if exists(select 1 from sys.tables where name=''Licence'')
begin

Insert into #results
Select
DB_NAME() as ''Databas'',
Company as ''CompanyCode'',
Title as ''Title''
'


use @DB; --This is what i'm struggling with because the if exists check below is checking against master, i need it to check the current @DB from curDB
if exists (select 'X' from sys.columns where name = 'DocumentLevelInvoiceMatching')

    begin
        set @sql = @sql + 'Case when Status = 1 then ''NewEngine'' else ''OldEngine'' end as ''MatchingEngine''
        from dsdba.companies
        end
        '
    end
else
    set @sql = @sql + '''Old Engine''
    from dsdba.companies
    end
    '


use master
exec (@sql)
fetch next from curDB into @DB
end

use CentralDatabase

IF OBJECT_ID('dbo.CompanyConfiguration', 'U') IS NOT NULL 
  DROP TABLE dbo.CompanyConfiguration; 

use master
select * into CentralDatabase.dbo.CompanyConfiguration
from #results

Select * from CentralDatabase.dbo.CompanyConfiguration
close curDB
deallocate curDB


drop table #results

Are the number and names of database really that dynamic that you need a dynamic loop? Are you saying that the databases you are interested in all have the same table but could have absolutely any name whatsoever? What I'm getting at is that you could make some assumptions about the database name and use that to filter the initial `select [name] from sysdatabases` — Nick.Mc, May 03 '16 at 10:30
They follow a naming convention but they are all named differently, P2P_xxxxxx_yy where xxxx is always different and yy represents a version of the product. There are also numerous other unrelated databases on the server which need filtering out based on the tables contained within them — Dave Leech, May 03 '16 at 10:35
Example, P2P_ThisOne_91 has a table called companies with no status field. P2P_AnotherOne_93 has a table called companies that does have the status. I need to query both and populate the status in the central DB — Dave Leech, May 03 '16 at 10:39
If you use `select [name] from sysdatabases where [name] like 'P2P%'`, will that be more reliable? — Nick.Mc, May 03 '16 at 11:43
Nope, I'm already getting all the databases I want from the existence of the .Licence table within the databases, this is about variations between the databases, older versions of the DB don't contain a field in the companies table but newer ones do and I need to report on all of them regardless of version, replacing missing fields with a value. Unfortunately an ISNULL wouldn't work due to the complete lack of the field in older databases or I wouldn't have this problem at all :( — Dave Leech, May 03 '16 at 13:04

score 0 · Accepted Answer · answered May 03 '16 at 11:40

You can move the check into your dynamic SQL.

Example

DECLARE @Qry NVARCHAR(MAX) = '
    USE ' + QUOTENAME(@DB) + ';

    IF EXISTS (SELECT 1 FROM sys.columns...) 
        BEGIN
            ...
        END
    ELSE
        BEGIN
            ...
        END
';

EXECUTE(@Qry);

USE cannot be combined with a variable, outside of dynamic sql, making the statement USE @DB invalid. This is because @DB is a string (NVARCHAR(128)) and USE expects a database. Unfortunately you cannot directly parametrise databases.

I've added the functon QUOTENAME to help prevent SQL injection attacks.

Yeah I thought about this approach, It would certainly do the job but I would need to repeat my select and I was trying to avoid repeating the same code. Thanks for the quotename, not used before but just had a quick google and will start doing! — Dave Leech, May 03 '16 at 12:51

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

The

USE @DatabaseName

can be issued from a dynamic EXECUTE statement, but then it will only have effect within the context of the statement you made (see How can I do something like: USE @databaseName)

In your case you can do something like:

DECLARE @exists BIT=0,
@tempSQL NVARCHAR(100)='use '+@DB+'; select @exists=''1'' from sys.columns where name = ''DocumentLevelInvoiceMatching''';

EXECUTE sp_executesql @tempSQL, N'@exists BIT OUTPUT', @exists OUTPUT;
PRINT @exists;

Then test the value of @exists

edited May 23 '17 at 12:13

Community

1
1

answered May 03 '16 at 12:02

Radu Jakab

64
1
5

This gives a syntax error on + @DB I think this should do what I want, but cant figure out why its throwing the error – Dave Leech May 03 '16 at 12:58
[sp_ExecuteSQL](https://msdn.microsoft.com/en-gb/library/ms188001.aspx) does not allow you to pass complex expressions (concatenation for exmaple). `EXECUTE sp_executesql N'SELECT TOP 10 Name FROM ' + @MyTable` raises an syntax error while `EXECUTE sp_executesql N'SELECT TOP 10 Name FROM MyTable'` does not. – David Rushton May 03 '16 at 13:27
Edited the solution to put the execute string in a variable first, this should clear the error. – Radu Jakab May 04 '16 at 06:43

Querying multiple databases on the same server

2 Answers2