Have you ever remembered what Intstance a table was on but not the database name? This will solve your problem. First you will create the table, then the procedure that will look for it and finally, you will execute the procedure with your table name. (At the end you can drop it so no one knows your secret.)
It returns the database, the schema, the table name and a type.
SET NOCOUNT ON
CREATE TABLE SearchTable(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) --If you haven't ever used this.
GO
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON
TRUNCATE TABLE SearchTable -- If you are using it again.
IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a table name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases
WHILE @dbname @dbname
END
SELECT * FROM SearchTable
usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURNGO
EXEC usp_FindMyTable 'Unicorn' -- put in the table you are searching for here. GO
SET NOCOUNT OFF
DROP PROC usp_FindMyTable --Clean up so no one knows
DROP TABLE SearchTable --Really, it is our secret. More clean up.
GO
