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
 

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/ZyO