SQL Script to draw info from multiple databases

danswan · 5 Jun 2019 at 19:49

Hello. I was hoping someone could help me with this, I can usually work this sort of thing out myself but I'm a bit stuck..

It's a work thing. We have a SQL Server with multiple databases, the schema of many being identical. I want to be able to easily query all of certain types of DB to get some information out. To elaborate...

Say the databases I'm interested in all end in 123_456, but they start with something else, (e.g. abc.123_456, def.123_456, etc etc)

Each of these databases contains a table called 'Parameters', and each 'Parameters' table contains a field called 'Version' (for sake or argument).

I want to be able to return lines from the 'Parameters' table where the 'Version' = 'XYZ'.

So my query needs to be something like: select * from Parameters where version = 'xyz'. What I don't know how to do is expand this to pick up the same info from all databases ending in 123_456. I'm assuming I can use something like UNION where DBName LIKE *.123_456, but I've struggled to get anywhere so far. I could be barking up the wrong tree so if there's a much better way please let me know!

radderfire · 5 Jun 2019 at 20:02

I have only ever run an SQL query against a single database. If it could be done, it would likely involve creating a temporary data structure in memory somehow, but it's not something I've ever looked into.

tom_e · 5 Jun 2019 at 20:04

Edit: ignore, was thinking different servers not dbs

Edit 2: if they're all on the same server it's as simple as

Select version from abc123.dbo.parameters

Union

Select version from cde123.dbo.parameters

danswan · 5 Jun 2019 at 20:18

tom_e said:
Edit: ignore, was thinking different servers not dbs

Edit 2: if they're all on the same server it's as simple as

Select version from abc123.dbo.parameters

Union

Select version from cde123.dbo.parameters

Thanks. Yes all the DBs are on the same SQL server instance. I had thought of union, however it's not quite that simple - the databases are added and removed frequently so this method would require me to declare all the DB names specifically and would be a lot of work to stay on top of. If at all possible I'd like something which could dynamically pick out databases where the name ends in 123_456

danswan · 6 Jun 2019 at 12:13

I'm getting close to what I need. I can get a single query to return all the info from all the relevant DBs, however it's coming back in management studio as multiple results windows. I need my results in a single window.

This is what is almost working for me:

Code:

DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%123_456'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM ' + @DB + '.dbo.Parameters'
    exec(  @sql )

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

AHarvey · 6 Jun 2019 at 16:34

Why not create a temp table and use:

SET @SQL = 'SELECT * INTO TEMP_TABLE FROM ' + @DB + '.dbo.Parameters'

Then do a simple 'SELECT * FROM TEMP_TABLE' at the end?

This may work if all the db's have access to each other, otherwise, it'll be squeezing a union in there somewhere but without access to the databases my sql isn't good enough (and I do it for a job

)

Goksly · 6 Jun 2019 at 16:35

Build up a string of the queries and use "EXEC" method to run it, so similar to what you've got but the while loop just amends the string and the EXEC is afterwards.

select * from table

select * from table union all select * from table

exec select * from table union all select * from table

If you get me...

FredFlint · 6 Jun 2019 at 19:12

I created an application called Script Runner, it's like SQL Management Studio but runs scripts against multiple DB's that can be on different hosts or not.

danswan · 7 Jun 2019 at 08:01

AHarvey said:
Why not create a temp table and use:

SET @SQL = 'SELECT * INTO TEMP_TABLE FROM ' + @DB + '.dbo.Parameters'

Then do a simple 'SELECT * FROM TEMP_TABLE' at the end?

This may work if all the db's have access to each other, otherwise, it'll be squeezing a union in there somewhere but without access to the databases my sql isn't good enough (and I do it for a job )

Thanks. I did try this but it fails after the first results are returned, with a message that the temp table already exists. Definitely some sort of union required, but I can't work out how to do this without declaring all the individual databases individually. Whilst I could do that, it's not going to be practical to maintain moving forward, I need it to dynamically pick up the DBs ending in 123_456..

Goksly said:
Build up a string of the queries and use "EXEC" method to run it, so similar to what you've got but the while loop just amends the string and the EXEC is afterwards.

select * from table

select * from table union all select * from table

exec select * from table union all select * from table

If you get me...

Sorry, don't get you!

FredFlint said:
I created an application called Script Runner, it's like SQL Management Studio but runs scripts against multiple DB's that can be on different hosts or not.

Thanks, but I know I'm almost there with a script, there should be no need for an additional application to do this!

tom_e · 7 Jun 2019 at 08:07

danswan said:
Thanks. I did try this but it fails after the first results are returned, with a message that the temp table already exists. Definitely some sort of union required, but I can't work out how to do this without declaring all the individual databases individually. Whilst I could do that, it's not going to be practical to maintain moving forward, I need it to dynamically pick up the DBs ending in 123_456..

I know you're not going this way but just for future reference to avoid this you write a create table statement at the start of the scrip to create the temp table and then "Insert into" rather than "select into".

tom_e · 7 Jun 2019 at 08:14

Code:

DECLARE @Results table
(
Parameter varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%123_456'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO @Results SELECT * FROM ' + @DB + '.dbo.Parameters'
    exec(  @sql )
    
    insert into #results
    values ((select top 1 parameter from @results))
    
    delete from @results

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results

Give that a shot.

danswan · 7 Jun 2019 at 08:39

Thank you! I feel like it must be nearly there, but I'm getting this repeatedly (once per matching DB):

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Results".

And at the end:
Msg 208, Level 16, State 0, Line 32
Invalid object name '#results'.

tom_e · 7 Jun 2019 at 09:02

danswan said:
Thank you! I feel like it must be nearly there, but I'm getting this repeatedly (once per matching DB):

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Results".

And at the end:
Msg 208, Level 16, State 0, Line 32
Invalid object name '#results'.

Edit: Ignore me thought I had it!

tom_e · 7 Jun 2019 at 09:15

Code:

if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Parameter varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Parameter varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%123_456'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #results1 SELECT top 5 key_id FROM ' + @DB + '.dbo.parameters'
    exec(  @sql )
    
    insert into #results
    values ((select top 1 parameter from #Results1))
    delete from #Results1

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results

danswan · 7 Jun 2019 at 13:25

This is fantastic, can't thank you enough. It is working to a certain extent, however there's a little tweaking required in the results. To elaborate...

top 5 key_id wasn't working (Pretty sure it wasn't supposed to?) so I changed the select to pull back a single value from the parameters table: Select account_number FROM ' + @DB + '.dbo.parameters'

This then worked perfectly - I got this single entry being returned from all of my relevant DBs! This is as close as I've been to getting exactly what I need out of my data. So logically I then tried selecting one more column: Select account_number, server_collection FROM ' + @DB + '.dbo.parameters'

This returns the following:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

I think there's a mismatch in the data in the two tables due to something I'm not understanding. Because I now have 2 columns of data, I did try:

insert into #results
values ((select top 2 parameter from #Results1))

but it returns the same message. Do you know why this is?

tom_e · 7 Jun 2019 at 13:30

Yeah sorry that was my test script that I was running against some of our dbs. You'll need to change the temp table create statements to include those 2 fields and remove the "parameter" field I've put in there.

So something like

Code:

if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
account_number varchar(50),
server_collection varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
account_number varchar(50),
server_collection varchar(50)
)

then

Code:

insert into #results
values ((select top 1 account_number,server_collection from #Results1))

The "TOP" command is done in rows so "TOP 1" gives you the first row "TOP 2" gives you the first 2, you then have to specify your columns in the select statement afterwards as normal.

danswan · 7 Jun 2019 at 17:32

Sorry to be a pain @tom_e, you have already shown more patience than most would, feel free to ignore and enjoy your weekend until you are back at work on Monday if you like

. I'm now getting this message:

Msg 207, Level 16, State 1, Line 30
Invalid column name 'Account_Number'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Server_Collection'.
Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 213, Level 16, State 1, Line 29
Column name or number of supplied values does not match table definition.

This is with the following SQL:

Code:

if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Account_Number varchar(50),
Server_Collection varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Account_Number varchar(50),
Server_Collection varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%123_456'
DECLARE @DB SYSNAME

OPEN DBS

FETCH NEXT FROM DBS INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'INSERT INTO #results1 SELECT Account_Number, Server_Collection FROM ' + @DB + '.dbo.parameters'
    exec(  @sql )
    
    insert into #results
    values ((select top 1 Account_Number, Server_Collection from #Results1))
    delete from #Results1

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results

tom_e · 7 Jun 2019 at 17:43

danswan said:
Sorry to be a pain @tom_e, you have already shown more patience than most would, feel free to ignore and enjoy your weekend until you are back at work on Monday if you like . I'm now getting this message:

Msg 207, Level 16, State 1, Line 30
Invalid column name 'Account_Number'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Server_Collection'.
Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 213, Level 16, State 1, Line 29
Column name or number of supplied values does not match table definition.

This is with the following SQL:

Code:

if object_id('tempdb..#Results') is not null drop table #Results CREATE TABLE #Results ( Account_Number varchar(50), Server_Collection varchar(50) ) if object_id('tempdb..#Results1') is not null drop table #Results1 create table #Results1 ( Account_Number varchar(50), Server_Collection varchar(50) ) DECLARE DBS CURSOR FOR SELECT name FROM sys.databases WHERE name like '%123_456' DECLARE @DB SYSNAME OPEN DBS FETCH NEXT FROM DBS INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'INSERT INTO #results1 SELECT Account_Number, Server_Collection FROM ' + @DB + '.dbo.parameters' exec( @sql ) insert into #results values ((select top 1 Account_Number, Server_Collection from #Results1)) delete from #Results1 FETCH NEXT FROM DBS INTO @DB END CLOSE DBS DEALLOCATE DBS select * from #results

I can't test this as I've not got sql at home but try running this

Code:

if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Account_Number varchar(50),
Server_Collection varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Account_Number varchar(50),
Server_Collection varchar(50)
)

On it's own then run the whole query, sometimes the temp tables stay cached and the changes don't get executed until you run the drop and create statement before the full script.

Randomface74 · 7 Jun 2019 at 18:12

I think you're generally getting there so I won't distract from that.
A couple of observations:
- You may want a column in #Results to store @DB so you know where the record came from.
- You may want to use the NOLOCK hint to try and avoid lock escalation on your Parameters tables.
Example: SELECT * FROM [Parameters] WITH (NOLOCK)

danswan · 7 Jun 2019 at 19:46

Hmm no dice. That seems to have stopped the 'Invalid Column' messages, but still getting the other errors. It seems to be this bit where it's falling over:

Code:

    insert into #results
    values ((select top 2 Account_Number, Server_Collection from #Results1))
    delete from #Results1

Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 213, Level 16, State 1, Line 29
Column name or number of supplied values does not match table definition.

Anyway I'm out until Monday morning now, so plenty time to think