How do you find which database a table is located in, of which you know the name (e.g. dbo.mytable1), in Microsoft SQL Server Management Studio 2005?

Question

I know the name of the table I want to find. I'm using Microsoft SQL Server Management Studio 2005, and I want to search all databases in the database server that I'm attached to in the studio. Is this possible? Do I need to query the system tables?

Answer 1

You can use the sp_MSforeacheachdb.

sp_MSforeachdb 'IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = ''TableName'') PRINT ''?''';

Answer 2

use master

DECLARE @db_name varchar(128) DECLARE @DbID int DECLARE @sql_string nvarchar(4000) DECLARE @TableName varchar(30)

Select @TableName = ''

set nocount on

CREATE TABLE [#tblDatabaseName] ( [DbName] [varchar] (128) NOT NULL , [TableName] [varchar] (128) NOT NULL )

declare db_cursor cursor forward_only for

SELECT  name, DbID 
FROM  master..sysdatabases
WHERE  name NOT IN ('northwind', 'pubs')
AND  (status & 32) <> 32      --loading.
AND (status & 64) <> 64     --pre recovery.
AND (status & 128) <> 128      --recovering.
AND (status & 256) <> 256      --not recovered.
AND (status & 512) <> 512     --Offline
AND (status & 32768) <> 32768  --emergency mode.
AND  DbID > 4

open db_cursor

fetch next from db_cursor into @db_name, @DbID

while @@FETCH_STATUS = 0 begin

set @sql_string = ''
+' Insert into #tblDatabaseName '
+' select  ''' + @db_name + ''' as ''DbName'',  '
+'         o.name as ''TableName''  '
+' from             [' + @db_name + ']..sysobjects   o with(nolock)  '
+' where  o.name like ''' + @TableName + '''  ' 

execute sp_executesql @sql_string

fetch next from db_cursor into @db_name, @DbID

end

deallocate db_cursor

select * from #tblDatabaseName

drop table #tblDatabaseName

Answer 3

sp_MSForEachDB is an undocumented proc that could do this for you. Getting the output out is a little harder so I'll leave that for you.

EXEC sp_MSForEachDB 'USE [?] IF EXISTS(SELECT * FROM Sys.Objects WHERE Type = ''U'' AND Name = ''Product'') PRINT ''?'''

Answer 4

As above but use system function not system tables

EXEC sp_MSForEachDB 'USE [?] IF OBJECT_ID(''dbo.mytable'') IS NOT NULL PRINT ''?'''