Search a table name in all databases at once

It's very difficult to select each and every database and search for the table from sys.tables something look like below 

Select * from sys.tables where name like '%TableName_Here%'

What if you don't know the exact database, do you search all the databases to find the table?

if your database server has 100 database's then it will be a very tedious task for you to select a database and search so to avoid such pain below is the procedure you can execute this procedure in any of the databases and execute the procedure by giving the table name as a parameter.

	CREATE PROCEDURE up_GetTableNameInAllDatabase
	@TableName VARCHAR(256)
	AS
	DECLARE @DataBaseName VARCHAR(256)
	DECLARE @SQL 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 @DataBaseName
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @SQL = 'USE ' + @DataBaseName + ';
	INSERT INTO #TmpTable
	SELECT '''+ @DataBaseName + ''' AS DBName,
	SCHEMA_NAME(schema_id) AS SchemaName,
	name AS TableName
	FROM sys.tables
	WHERE name LIKE ''%' + @TableName + '%'''
	EXEC (@SQL)
	FETCH NEXT
	FROM @getDBName INTO @DataBaseName
	END
	CLOSE @getDBName
	DEALLOCATE @getDBName
	SELECT *
	FROM #TmpTable
	DROP TABLE #TmpTable
	GO
	--EXEC up_GetTableNameInAllDatabase 'TableName_Here'

view raw GetTableNameInallDatabase hosted with ❤ by GitHub

Hope the above procedure will solve your problem cheers... !!!