Post reply

Run select statement across all databases

  • February 4, 2013 at 11:38 am

    #268120

    Hi All,

    Looking to run the following select statement across all databases. Not having any luck after researching.

    select * from t_payers where pay_name = 'Name of Payer'

    Any help would be appreciated!

  • February 4, 2013 at 11:46 am

    #1584070

    sp_msForEachDb

    'If EXISTS(SELECT 1 FROM [?].sys.tables WHERE name = ''t_payers'')

    select [?] As DbName,* from [?].dbo.t_payers where pay_name = ''Name of Payer''; '

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2013 at 11:50 am

    #1584073

    Thanks. After running that I am getting column name errors -

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'DB1'.

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'DB2'.

    Any idea?

  • February 4, 2013 at 11:54 am

    #1584075

    oops sorry; the first quesiton mark should be in quotes:

    sp_msForEachDb

    'If EXISTS(SELECT 1 FROM [?].sys.tables WHERE name = ''t_payers'')

    select ''[?]'' As DbName,* from [?].dbo.t_payers where pay_name = ''Name of Payer''; '

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2013 at 12:03 pm

    #1584077

    Awesome. Thanks! How do I give you rep? 🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply