Find where the table is

				
--**************************************
-- Name: Find where the table is
-- Description:Accepts a table Name and returns the database where the table is. This code is usefull when you have lots of databases and work with lots of DTS packages that perform changes on tables that exist in the various database. 
Where is that table ??? This store procedure will answer you in a second
-- By: Marcio Coelho
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=438&lngWId=5--for details.--**************************************

CREATE PROCEDURE sp_findtable
(
@TableName	varchar(50) = NULL
)
AS
--sp_findtable 'weblogcache'
SET NOCOUNT ON 
DECLARE @name		varchar(50)
DECLARE @sql		varchar(2000)
DECLARE @DBName	varchar(50)
SELECT @sql = ''
SELECT @DBName = ''
CREATE Table #temp
(
DBName		varchar(50),
TableName	varchar(50)
)
DECLARE DB_Cursor SCROLL CURSOR FOR 
-- Dpt,
SELECT [Name]
	 FROM Master.dbo.sysdatabases
	WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')
	
OPEN DB_Cursor
		FETCH FIRST FROM DB_Cursor
		INTO @Name
WHILE ( @@Fetch_Status = 0)
	BEGIN
		SELECT @DBName = @Name
		SELECT @sql =N' INSERT INTO #temp '
		SELECT @sql = @sql + ' SELECT '''+@DBName+''', [Name] FROM '+@DBName+'.dbo.sysobjects WHERE type = ''u'' '
		
		EXECUTE (@sql)
		
	
 		FETCH NEXT FROM DB_Cursor
		INTO @Name
	END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
IF @TableName IS NOT NULL
	SELECT * FROM #temp WHERE TableName = @TableName
IF @TableName IS NULL
	SELECT * FROM #temp ORDER BY DBNAME, TableName