Have table name wants to know DB name

Question

Post reply

Have table name wants to know DB name

ramadesai108

SSChampion

Points: 10609
More actions
December 3, 2010 at 8:58 am

#95673

I have a table name and I want to know which database contains that table. How do I find out?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Run a query against the information_schema.tables view in each database on the server to find out the one(s) it is in.

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 2

you can run the following query :

EXECUTE sp_msforeachdb 'select * from ?.information_schema.tables where table_name = ''articles'''

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

ramadesai108

SSChampion

Points: 10609

December 3, 2010 at 12:30 pm

#1258602

Thank you, that works.

chandan.behura SSC Rookie Points: 30 More actions · Answer 4

select * from information_schema.tables where table_name = 'articles'

Error starting at line 1 in command:

select * from information_schema.tables where table_name = 'articles'

Error at Command Line:1 Column:33

Error report:

SQL Error: ORA-00942: table or view does not exist

00942. 00000 - "table or view does not exist"

*Cause:

*Action:

i am running the above query in SQLDeveloper tool.

plz give alternate solution.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I'm guessing one or more of your databases have a case-sensitive collation. The name of the INFORMATION_SCHEMA schema and the views within it are upper case.

John

chandan.behura SSC Rookie Points: 30 More actions · Answer 6

NO IT IS ALSO NOT WORKING>>>SAME ERROR AGAIN

:crying:

but thanks for ur respond....

chandan

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

i am running the above query in SQLDeveloper tool.

What happens if you run it in SSMS? Also, that error message does not look like one generated by SQL Server. Are you sure you're running the query against a SQL Server database?

John