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?

• Shawn Melton

  SSC-Insane

  Points: 24685

  More actions

  September 1, 2010 at 1:44 pm

  #1215263

  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

  September 1, 2010 at 2:11 pm

  #1215279

  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

  September 1, 2010 at 5:12 pm

  #1215340

  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

  September 2, 2010 at 8:40 am

  #1215653

  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.