How can I find which database owns this table ?

Question

Post reply

How can I find which database owns this table ?

mw_sql_developer

SSCoach

Points: 19445
More actions
March 11, 2016 at 11:26 am

#313930

I was given this SQl statement. Could not find a table like that. We have about 25 databses in our SQl server. Is there a script that I can write to search all the databases in the server ? ( to see which db holds this table )
SELECT DISTINCT ReptMonth, StartChkDt
FROM ClmRejRept
ORDER BY StartChkDt DESC

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this will work:

if the database name shows up, it has a table with that name:

exec sp_MSforeachdb 'SELECT ''?'' As DatabaseName FROM [?].sys.tables WHERE name = ''ClmRejRept'' '

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!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Nice syntax...

I was just playing with the SQl and ran the following

exec sp_MSforeachdb 'SELECT top 1 ''?'' As DatabaseName FROM [?].sys.tables '

However it only listed about 6 of the databases. We have many more than that

Lowell SSC Guru Points: 323518 More actions · Answer 3

mw112009 (3/11/2016)
Nice syntax...
I was just playing with the SQl and ran the following
exec sp_MSforeachdb 'SELECT top 1 ''?'' As DatabaseName FROM [?].sys.tables '
However it only listed about 6 of the databases. We have many more than that

if you are not sysadmin, you might be getting just the databases you(yourlogin) have access to.

could that be in?

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!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

mw_sql_developer

SSCoach

Points: 19445

March 11, 2016 at 12:43 pm

#1864672

Makes sense.! Thanks for the perfect answer

Jon.Morisi SSChampion Points: 12846 More actions · Answer 5

I always do it like this:

DECLARE @command VARCHAR(1600)

SELECT @command = 'Use [' + '?' + ']

SELECT file_id,

name...

FROM sys.database_files'

EXEC sp_MSForEachDB @command

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Thx for the reply....

Jon.Morisi:

BTW More than your reply I was taken by the changing ICON you were using. That is cool!

May I wask what tool you used to create the animated GIF file.