SQL Script to draw info from multiple databases

[danswan]

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

 

[tom_e]

If I get 5 minutes I'll have a play on our dbs tomorrow, I'll have missed something glaringly obvious.

 

[FredFlint]

If you don't need persistent data in the results table and are using T-SQL you can use a table variables.

Edit: never mind, you are using tempDB so you use dynamic sql.

 

[tom_e]

@danswan
I knew I was missing something obvious, because I'd used the VALUES keyword after the INSERT it was falling over as the SELECT brought back 2 columns.

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
    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]

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

 

[danswan]

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

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

 

[tom_e]

It's because it's declared outside of that SQL statement. Write it in the same way you declare the DB to select from. So

'INSERT INTO #results1 SELECT Product_version, ' + @DB + ' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'

 

[danswan]

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

 

[tom_e]

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

'INSERT INTO #results1 SELECT Product_version, ''' + @DB + ''' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'

 

[danswan]

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

 

[soulhunter123]

This seems like the kind of thing which would be far easier to do using an intermediate programming language instead of trying to shoehorn it all into a single script (SQL).