search every database on server from a single sp

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[findnumber] @number VARCHAR(50)

AS 

 BEGIN

    



 DECLARE @db TABLE

 (

 id BIGINT IDENTITY(1, 1),

 dbname VARCHAR(50) NOT NULL

 )



 INSERT INTO @db ( dbname )

 EXECUTE

 ( 'USE [master] SELECT [name] FROM sys.databases WHERE database_id > 4 AND state_desc = ''ONLINE'''

 )



 DECLARE @found TABLE

 (

 id INT IDENTITY(1, 1),

 firstname VARCHAR(50) NULL,

 lastname VARCHAR(50) NULL,

 personid VARCHAR(50) NOT NULL,

 dbname VARCHAR(50) NOT NULL

 )



 DECLARE @counter INT

 SET @counter = 1

 DECLARE @maxid INT 

 SET @maxid = ( SELECT MAX(id)

 FROM @db

 )

 DECLARE @dbname VARCHAR(50)







 WHILE @counter <= @maxid

 BEGIN

 

 SET @dbname = ( SELECT dbname

 FROM @db

 WHERE id = @counter

 )

 

 DECLARE @TABLENAME VARCHAR(50)

 SET @TABLENAME = 'Person'

 

 

 EXECUTE

 ( 'USE ' + @dbname

 + ' SELECT * FROM sys.tables WHERE [name] = '''

 + @TABLENAME + ''''

 )

 IF @@ROWCOUNT > 0 

 BEGIN

 PRINT @@ROWCOUNT

 EXECUTE

 ( 'USE ' + @dbname

 + ' SELECT firstname, lastname, personid, '''

 + @dbname

 + ''' as dbname FROM person WHERE telcell LIKE '''

 + @number + ''' OR telhome LIKE ''' + @number

 + ''' OR telwork LIKE ''' + @number + ''''

 )

 IF @@ROWCOUNT >= 1 

 BEGIN

 INSERT INTO @found

 (

 firstname,

 lastname,

 personid,

 dbname

 )

 EXECUTE

 ( 'USE ' + @dbname

 + ' SELECT firstname, lastname, personid, '''

 + @dbname

 + ''' as dbname FROM person WHERE telcell LIKE '''

 + @number

 + ''' OR telhome LIKE '''

 + @number

 + ''' OR telwork LIKE '''

 + @number + ''''

 )

 END

 ELSE 

 BEGIN

 PRINT @number + ' NOT found IN DATABASE '

 + @dbname 

 END

 

 END 

 

 SET @counter = @counter + 1

 END



 SELECT *

 FROM @FOUND





 END