none
Run Query in ALL Database RRS feed

  • Question

  • Hi All,

    I have a many database with same structure & I want to run below query in all DB in One sql Statement.

    select * from customer

    How is it possible ?

    Thanks,

    Wednesday, March 13, 2013 9:38 AM

Answers

  • When you a single result set:

    SELECT * FROM databaseA..customer
    UNION ALL
    SELECT * FROM databaseB..customer
    UNION ALL
    ..

    You may use dynamic SQL to create this SQL:

    DECLARE @Sql NVARCHAR(MAX) = NULL;
    
    SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT * FROM ' + QUOTENAME([name]) + '..customer'
    FROM    master.sys.databases
    WHERE   NOT [name] IN ( 'master', 'tempdb', 'model', 'msdb' );
    
    PRINT @Sql;
    -- EXECUTE ( @Sql );


    Wednesday, March 13, 2013 10:06 AM
  • D'oh..

    DECLARE @Sql NVARCHAR(MAX) = NULL;
    
    SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT ''' + [name] + ''' AS dbname, [name] COLLATE Latin1_General_CI_AS AS [name] FROM ' + QUOTENAME([name]) + '.sys.tables '
    FROM    master.sys.databases
    WHERE   NOT [name] IN ( 'master', 'tempdb', 'model', 'msdb' );
    
    SET @Sql = 'CREATE VIEW TestView AS ' + @Sql;
    
    PRINT @Sql;
    --EXECUTE ( @Sql )

    Wednesday, March 13, 2013 1:06 PM

All replies

  • This is system  undocumented stored procedure that may removed in the future versions.

    exec master.dbo.sp_msforeachdb 'select *  from ?.customer'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, March 13, 2013 9:43 AM
    Answerer
  • sp_msforeachdb 'select *  from ?.dbo.product'

    http://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you




    Wednesday, March 13, 2013 9:56 AM
  • Hallo Ashish,

    another - pretty fine - option is the usage of "central management server".
    http://msdn.microsoft.com/en-us/library/bb934126.aspx

    There you can create groups and add your sql instances to the predefined groups.
    To execute a query on multiple servers of ONE group, right click on the group name and choose "new query"


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, March 13, 2013 9:58 AM
  • When you a single result set:

    SELECT * FROM databaseA..customer
    UNION ALL
    SELECT * FROM databaseB..customer
    UNION ALL
    ..

    You may use dynamic SQL to create this SQL:

    DECLARE @Sql NVARCHAR(MAX) = NULL;
    
    SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT * FROM ' + QUOTENAME([name]) + '..customer'
    FROM    master.sys.databases
    WHERE   NOT [name] IN ( 'master', 'tempdb', 'model', 'msdb' );
    
    PRINT @Sql;
    -- EXECUTE ( @Sql );


    Wednesday, March 13, 2013 10:06 AM
  • Thanks for this..

    But If I am using this statement then how to incorporate this dynamic sql into this..

    select  [GRP_ID]as Client_Name,YEAR(EFF_Date)*100+Month(EFF_Date) INCURRED_YEAR_MONTH,  
    coalesce(SP.[SUB_POPULATION],'')  AS SUB_POPULATION,RR.[RELATION_DESC],COUNT(MEMBER_KEY) AS MEMBER_MONTH from [USR].[EMP_GROUP],[USR].MEMBER_MONTH a inner join
    (select max(YEAR(EFF_Date)*100+Month(EFF_Date)) as maxdate from [USR].MEMBER_MONTH ) b
    on YEAR(EFF_Date)*100+Month(EFF_Date) = b.maxdate INNER JOIN [USR].[RFT_RELATION] AS RR ON RR.RELATION_KEY=a.RELATION_KEY
    INNER JOIN [USR].[RFT_Sub_Population] AS SP ON SP.SUB_POPULATION_KEY=a.SUB_POPULATION_KEY
    where MM_UNITS=1 and [GRP_ID] is not null
    group by [GRP_ID],YEAR(EFF_Date)*100+Month(EFF_Date),  coalesce(SP.[SUB_POPULATION],'') ,RR.[RELATION_DESC]
    Please Help..

    Wednesday, March 13, 2013 10:46 AM
  • Create a view with that dynamic SQL and use the view in your statement.
    Wednesday, March 13, 2013 10:56 AM
  • Can you please more elaborate this ??
    Wednesday, March 13, 2013 11:12 AM
  • Note that central Management Server is not needed for multi-server Query windows. Also, multi-server Query windows will only help when executing something against different instances, not different databases in the same instance...

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, March 13, 2013 11:13 AM
    Moderator
  • Try something like this,

    declare @sql varchar(max),@DBname varchar(max)
    declare DBS cursor for 
    select '['+name+']' from sys.databases where name not in ('master','tempdb','model','msdb')
    
    open dbs
    fetch next from dbs into @DBname
    while @@FETCH_STATUS = 0
    begin
    
    set @sql = 'SELECT [GRP_ID]                               AS Client_Name,
           Year(EFF_Date) * 100 + Month(EFF_Date) INCURRED_YEAR_MONTH,
           COALESCE(SP.[SUB_POPULATION], '')      AS SUB_POPULATION,
           RR.[RELATION_DESC],
           Count(MEMBER_KEY)                      AS MEMBER_MONTH
    FROM   '+@dbName+'.[USR].[EMP_GROUP],
           '+@dbName+'.[USR].MEMBER_MONTH a
           INNER JOIN (SELECT Max(Year(EFF_Date) * 100 + Month(EFF_Date)) AS maxdate
                       FROM   '+@dbName+'.[USR].MEMBER_MONTH) b
                   ON Year(EFF_Date) * 100 + Month(EFF_Date) = b.maxdate
           INNER JOIN '+@dbName+'.[USR].[RFT_RELATION] AS RR
                   ON RR.RELATION_KEY = a.RELATION_KEY
           INNER JOIN '+@dbName+'.[USR].[RFT_Sub_Population] AS SP
                   ON SP.SUB_POPULATION_KEY = a.SUB_POPULATION_KEY
    WHERE  MM_UNITS = 1
           AND [GRP_ID] IS NOT NULL
    GROUP  BY [GRP_ID],
              Year(EFF_Date) * 100 + Month(EFF_Date),
              COALESCE(SP.[SUB_POPULATION], ''),
              RR.[RELATION_DESC] '
    print @sql
    print '---------------------------------------------------------------------------------------'
    exec(@sql)
    fetch next from dbs into @DBname
    end          
    close dbs
    deallocate dbs


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Wednesday, March 13, 2013 12:36 PM
  • D'oh..

    DECLARE @Sql NVARCHAR(MAX) = NULL;
    
    SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT ''' + [name] + ''' AS dbname, [name] COLLATE Latin1_General_CI_AS AS [name] FROM ' + QUOTENAME([name]) + '.sys.tables '
    FROM    master.sys.databases
    WHERE   NOT [name] IN ( 'master', 'tempdb', 'model', 'msdb' );
    
    SET @Sql = 'CREATE VIEW TestView AS ' + @Sql;
    
    PRINT @Sql;
    --EXECUTE ( @Sql )

    Wednesday, March 13, 2013 1:06 PM
  • Note that central Management Server is not needed for multi-server Query windows. Also, multi-server Query windows will only help when executing something against different instances, not different databases in the same instance...

    Tibor Karaszi, SQL Server MVP | web | blog

    hallo Tibor,

    for sure - it's not fundamental for querying multiple servers but it is a good approach for a better management of sql server instances :)

    But I've missread the requirement. I didn't read that access to multiple databases in the same instance was required.


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, March 13, 2013 3:47 PM
  • "for sure - it's not fundamental for querying multiple servers but it is a good approach for a better management of sql server instances :)"

    Agreed Uwe. :-)


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, March 14, 2013 2:52 PM
    Moderator
  • this is my example script that I want to run on all databases:

    SELECT
    SI.port
    ,MM.name module_name
    ,MM.id module_id
    ,1 section_id
    ,'Headers' section_name
    ,MHF.field_id
    ,MHF.order_pos
    ,MHF.text
    ,MHF.type
    FROM MODULE.MODULES MM
    JOIN MODULE.HEADER_FIELDS MHF ON MM.id = MHF.module_id 
    LEFT JOIN MODULE.HEADER_FIELDS_DECISIONS MHFD ON  MHF.module_id = MHFD.module_id AND MHF.field_id = MHFD.field_id
    LEFT JOIN MODULE.HEADER_FIELDS_OPTIONS MHFO ON  MHF.module_id = MHFO.module_id AND MHF.field_id = MHFO.field_id
    CROSS JOIN (select port from SYSTEM.INFO with (NoLock, ReadUncommitted)) SI
    WHERE MM.name like '%order%'
    ---------------------------------------------------------------------------------------------------------------------LINES
    UNION ALL
    SELECT 
    SI.port
    ,MM.name module_name
    ,MM.id module_id
    ,2 section_id
    ,'Lines' section_name
    ,MLF.field_id
    ,MLF.order_pos
    ,MLF.text
    ,MLF.type
    FROM MODULE.MODULES MM
    JOIN MODULE.LINE_FIELDS MLF ON MM.id = MLF.module_id 
    LEFT JOIN MODULE.LINE_FIELDS_DECISIONS MLFD ON  MLF.module_id = MLFD.module_id AND MLF.field_id = MLFD.field_id
    LEFT JOIN MODULE.LINE_FIELDS_OPTIONS MLFO ON  MLF.module_id = MLFO.module_id AND MLF.field_id = MLFO.field_id
    CROSS JOIN (select port from SYSTEM.INFO with (NoLock, ReadUncommitted)) SI
    WHERE MM.name like '%order%'
    ---------------------------------------------------------------------------------------------------------------------FOOTERS
    UNION ALL
    SELECT 
    SI.port
    ,MM.name module_name
    ,MM.id module_id
    ,3 section_id
    ,'Footers' section_name
    ,MFF.field_id
    ,MFF.order_pos
    ,MFF.text
    ,MFF.type
    FROM MODULE.MODULES MM
    JOIN MODULE.FOOTER_FIELDS MFF ON MM.id = MFF.module_id 
    LEFT JOIN MODULE.FOOTER_FIELDS_DECISIONS MFFD ON  MFF.module_id = MFFD.module_id AND MFF.field_id = MFFD.field_id
    LEFT JOIN MODULE.FOOTER_FIELDS_OPTIONS MFFO ON  MFF.module_id = MFFO.module_id AND MFF.field_id = MFFO.field_id
    CROSS JOIN (select port from SYSTEM.INFO with (NoLock, ReadUncommitted)) SI
    WHERE MM.name like '%order%'

    ORDER BY module_id, section_id, order_pos

    Tuesday, September 6, 2016 5:40 AM