Technical Article

search every database on server from a single sp

,

This script has been designed for our databases in our environment, but it can easily be modified to suit your needs. unfortunately you will have to be familiar at a fairly advanced level to make your changes....

briefly put the principals are: go through every database that is not a system one, check if it contains the table you are looking for (we have a number of similar databases based on a template), check if it contains the right columns (exculded in this version), then see if it finds the string you are looking for.

please note this is very specific to our work environment and you will have to make changes. if you need help just contact me and i will see if i can help.

Chris

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

You rated this post out of 5. Change rating

Related content