Find Table in Every Database of SQL Server

Using below sql script we will get the table which contains the search string. Some time we are not known this table belongs from which database. With the help of below query we will get the list of database.

CREATE PROCEDURE SP_FindTableName @TableName VARCHAR(256) AS DECLARE @DBName VARCHAR(256) DECLARE @varSQL VARCHAR(512) DECLARE @getDBName CURSOR SET @getDBName = CURSOR FOR SELECT name FROM sys.databases CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256)) OPEN @getDBName FETCH NEXT FROM @getDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @varSQL = 'USE ' + @DBName + '; INSERT INTO #TmpTable SELECT '''+ @DBName + ''' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name LIKE ''%' + @TableName + '%''' EXEC (@varSQL) FETCH NEXT FROM @getDBName INTO @DBName END CLOSE @getDBName DEALLOCATE @getDBName SELECT * FROM #TmpTable DROP TABLE #TmpTable GO

Creating the SP.

Created successfully.

  

Now I am executing this SP.

It will return the all table names which contains “emp” .