How can I find where the table is ? Which database ?

Question

How can I find where the table is ? Which database ?

mw_sql_developer

SSCoach

Points: 19445
More actions
July 26, 2016 at 12:00 pm

#320129

I have been given a table name HCWKP
However I have no idea which database holds the table ?
How can we write some SQL to find which database holds the table ?

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

qucik google threw up this

https://social.technet.microsoft.com/wiki/contents/articles/17958.find-a-table-on-a-sql-server-across-all-databases.aspx

any good for you?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Or you could try SQL Search (free product): http://www.red-gate.com/products/sql-development/sql-search/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Luis Cazares (7/26/2016)
Or you could try SQL Search (free product): http://www.red-gate.com/products/sql-development/sql-search/

Disclosure, I work for Redgate.

This will search all databases on your instance if you like. You'll have to connect to each instance separately, but once there, you can search in any database.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

EXEC sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%HENCDP%'''

This works fine