Find Object Name Across All Databases On Server

FIND OBJECT NAME ACROSS ALL DATABASES ON SERVER

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), tblName nvarchar(200), cols int, maxRowSize int)

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

SELECT @dbTmp = dbName from #dbTmp where id = @cnt

SELECT @var = 'INSERT INTO #dbName (dbName, tblName) '

SELECT @var = @var + 'SELECT ''' + @dbTmp +''', OBJECT_NAME (sc.[object_id]) tablename FROM ['

+@dbTmp+'].sys.all_objects sc where Name like ''%CNTRY02%'''

PRINT @var

EXECUTE (@var)

SELECT @cnt = @cnt - 1

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName

 

--DB