Search All Databases for a Table
I just had a user send me an application error stating that a table the process was trying to create already existed. The user running the process did not know which of the five application databases the process was referring to and not wanting to run a query against all five databases I created the following simple stored procedure that searches all the databases on a server for a given table name. It has been tested on SQL 2k5 & SQL 2k8.
Create PROCEDURE [Util].usp_SearchAllDBforTable
@tablename sysname
AS
declare @cmd1 varchar(500)
set @cmd1 = ‘USE ?
SELECT [TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[TABLE_TYPE]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_NAME] = ”’ + @tablename + ””
exec sp_MSforeachdb @command1=@cmd1
GO
