none
Run query all databases ata time and find the databases.

    Question

  • Select * from test where itemkey='sess_key'

    I want to run this query on all databases and find which database the value('Sess_Key') still exist.


    Naveen| Press Yes if the post is useful.

    Tuesday, August 05, 2014 12:16 AM

Answers

  • Hi,

    THe idea of using Stored Procedure like sp_msforeachdb or sp_foreachdb is to go over all databases without the need of knowing which database you have (dynamically get all databases). In my opinion even if you have 100 specific database that you need to execute a specific query then you should not use those SP.

    You can use Dynamic SQL Query and build your SP which get database name (or list of database names in one time) as input, and execute your dynamic query on each of those databases.

    >> for 10 specific known databases you can just write the query yourself. The only thing you need is to use 3 part name (<database>.<schema>.<table name>)

    The query that you need is probably (if I understood you correctly) something like this:

    delete <database name>.<Schema name>.<table1 name>
    where itemkey='sess_key'

    this you can execute 10 time on 10 databases without using dynamic SP p_msforeachdb or sp_foreachdb. For example:

    delete database1.dbo.test
    where itemkey='sess_key'
    
    delete database2.dbo.test
    where itemkey='sess_key'
    
    delete database3.dbo.test
    where itemkey='sess_key'
    .
    .
    .
    delete database9.dbo.test
    where itemkey='sess_key'
    
    delete database10.dbo.test
    where itemkey='sess_key'
    
    


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, August 06, 2014 6:49 AM
    Moderator

All replies

  • Try:

    EXECUTE sp_msforeachdb '
    USE ?
    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''test'')
    	IF EXISTS (Select * from test where itemkey=''sess_key'')
    		PRINT DB_NAME()'

    This prints a list of databases that has a table named 'test' and rows with itemkey='sess_key'

    Hope this helps.
    ~ J.

    Tuesday, August 05, 2014 12:33 AM
  • Hi,

    sp_msforeachdb is not considered reliable. please check this blog and the code for sp_foreachdb

    http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, August 05, 2014 3:49 AM
    Moderator
  • Hi,

    One other option. You can download redgate tool trial version. There is a tool called sql multi script. You can use it to achieve the same.

    Regards,

    Brindha.

    Tuesday, August 05, 2014 8:21 AM
  • I want to run on specific 10 databases only.

    EXECUTE sp_msforeachdb '
    USE ?
    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''test'')
    IF EXISTS (Select * from test where itemkey=''sess_key'')
    PRINT DB_NAME()'

    Want to delete this records on 10 databases:

    Select * from test where itemkey=''sess_key'' -- want to delete the records.


    Naveen| Press Yes if the post is useful.


    • Edited by N_14 Tuesday, August 05, 2014 10:58 PM
    Tuesday, August 05, 2014 10:57 PM
  • Hi,

    THe idea of using Stored Procedure like sp_msforeachdb or sp_foreachdb is to go over all databases without the need of knowing which database you have (dynamically get all databases). In my opinion even if you have 100 specific database that you need to execute a specific query then you should not use those SP.

    You can use Dynamic SQL Query and build your SP which get database name (or list of database names in one time) as input, and execute your dynamic query on each of those databases.

    >> for 10 specific known databases you can just write the query yourself. The only thing you need is to use 3 part name (<database>.<schema>.<table name>)

    The query that you need is probably (if I understood you correctly) something like this:

    delete <database name>.<Schema name>.<table1 name>
    where itemkey='sess_key'

    this you can execute 10 time on 10 databases without using dynamic SP p_msforeachdb or sp_foreachdb. For example:

    delete database1.dbo.test
    where itemkey='sess_key'
    
    delete database2.dbo.test
    where itemkey='sess_key'
    
    delete database3.dbo.test
    where itemkey='sess_key'
    .
    .
    .
    delete database9.dbo.test
    where itemkey='sess_key'
    
    delete database10.dbo.test
    where itemkey='sess_key'
    
    


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, August 06, 2014 6:49 AM
    Moderator