Avatar of samprg
samprg
 asked on

Search in Databases

Hi,

I have many databases, I wanna use this query in the below, how?
Thanks
select name
from t1
where name='David'
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
jorgedeoliveiraborges

8/22/2022 - Mon
samprg

ASKER
I wanna use the query in all the databases
ralmada

you can try using msforeachdb

EXEC sp_MSforeachdb 'IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(''T1'') Select name from [?]..t1 where name = ''David'''
ralmada

missed a bracket there

EXEC sp_MSforeachdb 'IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(''T1'')) Select name from [?]..t1 where name = ''David'''
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
samprg

ASKER
Perfect, but I need to add a database name
ralmada

then do it like this

EXEC sp_MSforeachdb 'IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(''T1'')) Select db_name(), name from [?]..t1 where name = ''David'''
jorgedeoliveiraborges


select 'select name from ' + d.name + '.dbo.t1 where name = ' + char (39) + 'david'+ char (39) + '; '
from sys.databases d
where d.name not in ('master', 'model', 'msdb', 'tempdb')
order by 1;

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ralmada

sorry, it should be

EXEC sp_MSforeachdb 'IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(''T1'')) Select ? as dbname, name from [?]..t1 where name = ''David'''
samprg

ASKER
ralmada,
Gives me error
Invalid column name
samprg

ASKER
jorgedeoliveiraborges,

Does not work
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
ralmada

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
samprg

ASKER
Awesome
jorgedeoliveiraborges

>> jorgedeoliveiraborges,
>> Does not work
Sorry.

select 'select name from ' + d.name + '.dbo.t1 where name = ' + char (39) + 'david'+ char (39) + '; '
from master.sys.databases d
where d.name not in ('master', 'model', 'msdb', 'tempdb')
order by 1;

Open in new window