Find database name for a given object

Question

Post reply

Find database name for a given object

nate.allen

SSC Veteran

Points: 247
More actions
September 1, 2010 at 1:22 pm

#227069

So I have a database server with several different databases on it.
I have the name of an object, say a view. Basically I need a query to see what database on this database server that the view is on.
I can't seem to find the relation between sys.all_objects and sys.databases
Anyone know?

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

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

You can use the undocumented sp_MSForeachdb procedure and then do something like:

EXEC sp_MSForeachdb '

USE ?

SELECT DB_NAME()

SELECT * from sysobjects

WHERE name like "%objectname%"'

'

Then this should only return data back for the database that has the object. I use the SELECT DB_NAME() to get what database the SELECT statement is called against.

There is probably a cleaner way of doing it, this is just something I thought might work :hehe:

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

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

This should get you started.

declare @sql varchar(max)

select @sql =

'

declare @t table ([objectname]NVARCHAR(1000), seq int not null identity(1,1) primary key clustered )

'

select

@sql = @sql +

'

insert into @t ( [objectname] )

select quotename(TABLE_CATALOG)+''.''+quotename(TABLE_SCHEMA)+''.''+quotename(TABLE_NAME)

from '+quotename(a.name)+'.information_schema.tables

where TABLE_TYPE = ''VIEW''

'

from

sys.databases a

where

state_desc = 'online' and

user_access_desc = 'multi_user'

order by

a.name

select @sql = @sql+

'

select * from @t order by seq

'

exec ( @sql )

David Atkinson Ten Centuries Points: 1128 More actions · Answer 3

You could try SQL Search, which is a free tool we offer here at Red Gate.

http://www.red-gate.com/products/SQL_Search/

I'd be interested to get your feedback if it doesn't match your requirements.

Regards,

David

nate.allen SSC Veteran Points: 247 More actions · Answer 4

Thanks for the quick response on solutions, it really helped.

Michael's solution ended up being the best for what I was doing.

FYI...all the developers have been trying to convince the higher ups to get the red gate source control...I've heard good things about it.