GetTableNameInallDatabase

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'