Find database name for a given object

Question

Post reply

Find database name for a given object

nate.allen

SSC Enthusiast

Points: 187
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: 24675 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 )