You’ve been Thunderstruck and can now Find any Table in an Instance

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

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s