1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Script to draw info from multiple databases

Discussion in 'HTML, Graphics & Programming' started by danswan, Jun 5, 2019.

  1. danswan

    Hitman

    Joined: Feb 6, 2004

    Posts: 854

    Location: Toon

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

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 26,119

    Location: West mids

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

    Wise Guy

    Joined: Feb 1, 2006

    Posts: 1,957

    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: Jun 10, 2019 at 9:22 PM
  4. tom_e

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 26,119

    Location: West mids

    @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
     
  5. danswan

    Hitman

    Joined: Feb 6, 2004

    Posts: 854

    Location: Toon

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

    Hitman

    Joined: Feb 6, 2004

    Posts: 854

    Location: Toon

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

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 26,119

    Location: West mids

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

    Hitman

    Joined: Feb 6, 2004

    Posts: 854

    Location: Toon

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

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 26,119

    Location: West mids

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

    Hitman

    Joined: Feb 6, 2004

    Posts: 854

    Location: Toon

    **Edit** Think I've got it working now. Watch this space :D
     
    Last edited: Jun 12, 2019 at 1:56 PM