How to find Table Names in all the databases in SQL SERVER?

Run the following query on your SQL SERVER to get desired result:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_prc_FindTableNameInAllDatabase]  
@TableName VARCHAR(256)  
AS  
DECLARE @DBName VARCHAR(256)  
DECLARE @varSQL VARCHAR(max)  
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 ''%' + ltrim(rtrim(@TableName)) + '%'''  
EXEC (@varSQL)  
FETCH NEXT  
FROM @getDBName INTO @DBName  
END  
CLOSE @getDBName  
DEALLOCATE @getDBName  
SELECT DBName,SchemaName,TableName,COUNT(*) as TotalCount
FROM #TmpTable  
group by   DBName,SchemaName,TableName


DROP TABLE #TmpTable