Script to Search Through all Databases for a Table Name

-- Lets create a temporary holder for the database names
DECLARE @database TABLE (dbName VARCHAR(60))
DECLARE @db_name VARCHAR(50)
DECLARE @sql VARCHAR(MAX)

-- lets select non system databases and insert into the holder. we are exluding master,tempDB,MSDB and  model
SELECT  @sql = 'SELECT [Name] FROM sys.databases WHERE database_id > 4'

INSERT INTO @database
EXEC (@sql)

-- we are going to use a cursor to loop through and concantenate database name, schema name and table name
DECLARE @final_holder TABLE (db_Name_Schema_Name_Table_Name VARCHAR(100))

DECLARE db_cursor CURSOR FOR

SELECT DbName FROM @database

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@fetch_status = 0

BEGIN

DECLARE @vars nvarchar(max)
SELECT @vars = 'select table_catalog +''.''+table_schema + ''.'' + table_name from ' +@db_Name+ '.INFORMATION_SCHEMA.TABLES where table_type = ''BASE TABLE'''

INSERT INTO @final_holder
EXEC (@vars)

FETCH NEXT FROM db_cursor INTO @db_name

END
CLOSE db_cursor
DEALLOCATE db_cursor

-- now lets select the table name that we are searching for.
SELECT db_Name_Schema_Name_Table_Name FROM 
 (
SELECT * FROM @final_holder
) t
WHERE db_Name_Schema_Name_Table_Name LIKE '%Sales.Cust%'

-- results for searching string 'Sales.Cust'
/*
AdventureWorks.Sales.Customer
AdventureWorks.Sales.CustomerAddress
SIBS.Sales.Customer
SIBS.Sales.CustomerAddress
*/