Locating a table in the database

Question

When I run the following query I get the results as expected.

select * from [Beep].[Bopp]

Then, I ran the query below to "find" the table.

select * from sys.objects 
where type='U'
and name like '%Bopp%'

It finds the table and lists the row describing it. However, I can't see any reference to Beep there. The name only contains Bopp, so I'm guessing that there a key that I need to look up but I don't know which column that is nor in what table to look it up.

edit

Based on the comments, I improved the query but I'm still not sure in what table to look up the actual name of the schema. The following gives me waaay to many hits (and setting the type didn't actually give me anything Beep-like.

select * from sys.tables t
left join sys.objects s 
  on t.schema_id = s.schema_id
where t.name like '%Bopp%'

I checked the objects for the specific name like so.

select * from sys.objects
where name like '%Beep%'

To my surprise, I didn't see any hits at all. Where is the little Beep-y thing hiding?!

Beep is the schema. Check schema_id. Why are you using `sys.objects` instead of `sys.tables` anyway? `sys.objects` contains generic information — Panagiotis Kanavos, Jun 30 '15 at 14:59
@PanagiotisKanavos I'm using that because of [this reason](http://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-tables-in-a-database-using-tsql) (first hit on Google for "sql server table name"). Yours is probably better - wasn't aware of it. — Konrad Viltersten, Jun 30 '15 at 15:07

score 4 · Answer 1 · answered Jun 30 '15 at 15:01

4

You can use the schema_name() function against the schema_id column of sys.objects, like so:

select schema_name([schema_id]) from sys.objects where [name] like '%Bopp%';

Alternatively, you can query INFORMATION_SCHEMA.TABLES, which has a TABLE_SCHEMA column that gives the name of the schema rather than its id.

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Bopp';

answered Jun 30 '15 at 15:01

Joe Farrell

3,502
1
15
25

Actually, yours solved my issue too but I already accepted @emacsuser reply and it won't let me change it (although you'll have the moral victory here because I see your post is dated one hour prior to theirs). No idea how I could have missed it. Sorry. – Konrad Viltersten Jun 30 '15 at 18:40
No worries! I'm glad it was helpful. – Joe Farrell Jun 30 '15 at 18:47

score 1 · Accepted Answer · answered Jun 30 '15 at 16:17

Beep is the schema name for which you should attach another sys table like so:

select sys.schemas.name as schema_name, sys.objects.name as object_name
from sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type='U'
and sys.objects.name like '%Bopp%'

Locating a table in the database

edit

2 Answers2