SQL Script to draw info from multiple databases

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)
Click to expand...

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...
 
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.
 
Last edited:
@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.

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
    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
 
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
 
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
Code: 
'INSERT INTO #results1 SELECT Product_version, ' + @DB + ' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
 
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 said:
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
Click to expand...
Code: 
'INSERT INTO #results1 SELECT Product_version, ''' + @DB + ''' as name_of_DB FROM ' + @DB + '.dbo.DBVersion'
 
You must log in or register to reply here.
Back
Top Bottom