Goal: How to find a table on your server:
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 myTable
GO
SET NOCOUNT OFF
DROP PROC usp_FindMyTable
DROP TABLE myTable99
GO