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!

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

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!

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'.

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?

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

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

danswan · 10 Jun 2019 at 16:38

billysielu said:
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)

Thanks - I don't need the DB identifier, as the Account number will effectively do that. I will look to throw a NOLOCK in there if I see any locks. My main problem is as above, can't get this to return any results. I'm sure it's almost there...

danswan · 11 Jun 2019 at 08:35

It works! Many, many thanks @tom_e, you can't imagine how much time this is going to save me. Kudos

danswan · 12 Jun 2019 at 07:41

Quick one (hopefully!) for @billysielu, if you are still around assistance would be gratefully received. Whilst I did say I didn't need to include the DB name in this, I have moved onto another similar query where this would actually be useful. As @DB has already been defined, I thought I could just add it to the select statement, a bit like the below - however it's saying 'Must declare the scalar variable "@DB".'

Code:

if object_id('tempdb..#Results') is not null drop table #Results
CREATE TABLE #Results
(
Product_version varchar(50),
name_of_DB varchar(50)
)
if object_id('tempdb..#Results1') is not null drop table #Results1
create table #Results1
(
Product_version varchar(50),
name_of_DB varchar(50)
)
DECLARE DBS CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name like '%Manager'
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 Product_version, @DB as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
    exec(  @sql )
   
    insert into #results
    select top 1 Product_Version, name_of_DB from #Results1
    delete from #Results1

    FETCH NEXT FROM DBS INTO @DB
END

CLOSE  DBS
DEALLOCATE DBS

select * from #results

danswan · 12 Jun 2019 at 09:19

I had tried that, it seemed to break something in that I get multiple 'Invalid Column name' errors, which didn't actually reference column names - they referenced DB names - e.g.

Invalid column name abc_Manager,
invalid column name 123_Manager

danswan · 12 Jun 2019 at 11:27

**Edit** Think I've got it working now. Watch this space