Find database name for a given object

  • 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?

  • 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

  • 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 )

  • 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

  • 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.

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

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