USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50))
GO


CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON

TRUNCATE TABLE myTable99

IF @TABLE_NAME IS NULL
  BEGIN
	PRINT 'No Table to look for.  Please supply a tabke 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 < = @MAX_dbname
  BEGIN
	SELECT @sql = 'SET NOCOUNT ON '								   + CHAR(13) 
		+ 'INSERT INTO myTable99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)'	   + CHAR(13)
		+ 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE ' 		   + CHAR(13)
		+ 'FROM ' + @dbname + '.INFORMATION_SCHEMA.Tables' 				   + CHAR(13)
		+ 'WHERE TABLE_NAME LIKE ''' + @TABLE_NAME + '%' + '''' 			   + CHAR(13) 
--	SELECT @sql
	EXEC(@sql)
	SELECT @dbname = MIN([name]) FROM master..sysdatabases WHERE [name] > @dbname
  END

SELECT * FROM myTable99

usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURN
GO

EXEC usp_FindMyTable

EXEC usp_FindMyTable Orders
GO

SET NOCOUNT OFF
DROP PROC usp_FindMyTable
DROP TABLE myTable99
GO