two sp_MSforeachdb questions 1

[TysonLPrice]

I'm using SQL 2005.

Question #1
Occasionally we are asked to scan our databases for strings. For example recently it was for the User ID which was being expanded to 20 characters. We use a version of sp_Findsp that was copied out. I converted it to search all the databases and it seems to work except the message '* Stored procedures containing string " doesn't print. It does find all the SPs with the string though.

exec sp_MSforeachdb
'declare	@s varchar(255) 
set @s = ''UserLUP'' 
DECLARE	@msg varchar(255) ,
		@ul varchar(255)
select @s=''%'' + @s + ''%''
select	''SP Name''=upper(o.name), 
		Seq=colid ,
		''SP Line''=substring(text,patindex(@s,text)-1, 60)
from	?..syscomments  c (nolock) , 
		?..sysobjects  o (nolock)
where	o.id=c.id
and patindex(@s,text) > 0
and o.name in (select Name from ?..sysobjects where xtype = ''P'' )
order by name
SELECT @msg=''* Stored procedures containing string "'' + @s + ''='' + 
convert(varchar(8),@@rowcount) + '' *''
SELECT @ul=replicate(''*'',datalength(@msg))
Print '' ''
PRINT @ul
PRINT @msg
Print @ul'

What needs to be added to list the database name along with the stored procedure and text string?

Question #2

Could I loop through the DBs using just sp_Findsp? I tried this and it doesn't work. It just displays the same SPs for each DB.

exec sp_MSforeachdb 'sp_findsp ''UserLUP'''

I read where I need to use '?' to make it loop through as I did in the top one I converted but I don't know how to use it in this case.

 

[TysonLPrice]

Strange...Running this:

sp_findsp 'User'

Lists all the SPs.

Using this:

exec sp_MSforeachdb '?..sp_findsp ''User'''

Just lists the counts. For exaample:

***************************************************
* Stored procedures containing string "%User%=628 *
***************************************************

Hmmm

 

[SajidAttar]

Hi..

You need to concatenate the database where you are printing the message that says your '* Stored procedures containing...'

Change the following line in your procedure sp_FindSP

SELECT @msg=''* Stored procedures containing string "'' + @s + ''='' + 
convert(varchar(8),@@rowcount) + '' *''

to this

SELECT @msg=''* Stored procedures containing string "'' + @s + ''='' + 
convert(varchar(8),@@rowcount) + '' in Database '' + db_name() + '' *''

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[TysonLPrice]

SajidAttar,

Thanks for your input. I had mentioned that one of the issues I had was that message isn't printing at all that I see. What I really want is the name of the data base the SP was found on in the detail. I tried adding DB-Name() to the select as below but it just prints the same name for each database (the one I'm running it on). I need the name of the one the stored procedure is located. I think that's where the '?' comes in. Here's what I have now:

exec sp_MSforeachdb
'declare	@s varchar(255) 
declare	@DB varchar(50) 
set @DB = (select db_Name())
set @s = ''UserLUP'' 
DECLARE	@msg varchar(255) ,
		@ul varchar(255)
select @s=''%'' + @s + ''%''
select	@DB,''SP Name''=upper(o.name), 
		Seq=colid ,
		''SP Line''=substring(text,patindex(@s,text)-1, 60)
from	?..syscomments  c (nolock) , 
		?..sysobjects  o (nolock)
where	o.id=c.id
and patindex(@s,text) > 0
and o.name in (select Name from ?..sysobjects where xtype = ''P'' )
order by name
print ''Here''
SELECT @msg=''* Stored procedures containing string "'' + @s + ''='' + 
convert(varchar(8),@@rowcount) + '' in Database '' + db_name() + '' *''
SELECT @ul=replicate(''*'',datalength(@msg))
Print '' ''
PRINT @ul
PRINT @msg
Print @ul'

Here is sample output:

DB SP
Manhattan BILL_PUSHTX
Manhattan BILL_PUSHTX
Manhattan BILL_REVERSEVOUCHER

It lists Manhattan no matter which DB it is on.

 

[SajidAttar]

Since you are running the code from one database and using the four part object naming for sys tables.. you can not get the corerct database for db_name()..

Try this..

exec sp_MSforeachdb
'Declare @String varchar(255), @Message varchar(1000), @ul varchar(1000), @RecCount int
Select @String = ''Tab1''
Select @String =''%'' + @String+ ''%'',
	@RecCount = 0

Select ''?'' DBName, so.Name as ProcedureName, ColID as Seq, SubString(sc.Text,PatIndex(''%#Tab1%'',sc.Text)-1, 60)
From ?..SysObjects SO
Inner Join ?..SysComments SC on so.Id = sc.Id
Where so.Xtype = ''P''
	and PatIndex(''%#Tab1%'', sc.Text) > 0

Select @RecCount = @@RowCount


If @RecCount > 0
Begin
	Select @Message=''* ''+ convert(varchar(8),@RecCount) + '' Stored procedure(s) found in  database ? having the string "'' + @String + ''" *''
	Select @ul = Replicate(''*'',DataLength(@Message))

	PRINT @ul
	PRINT @Message
	Print @ul
End'

This worked fine for me and gave me follwing result ..

***********************************************************************************
* 1 Stored procedure(s) found in database XXXXXX_test having the string "%Tab1%" *
***********************************************************************************

It also return bunch of grids with nothing in it.. and only one grid in result pane had a result..

(it returns blank result for all other databases if nothing found.)

Hope this will help..

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[SajidAttar]

I am sorry.. there were some hardcodes in above code.. here is the w/o h/c ..

exec sp_MSforeachdb
'Declare @String varchar(255), @Message varchar(1000), @ul varchar(1000), @RecCount int
Select @String = ''#Tab1''
Select @String =''%'' + @String+ ''%'',
	@RecCount = 0
Print ''?''
Select ''?'' DBName, so.Name as ProcedureName, ColID as Seq, SubString(sc.Text,PatIndex( @String,sc.Text)-1, 60)
From ?..SysObjects SO
Inner Join ?..SysComments SC on so.Id = sc.Id
Where so.Xtype = ''P''
	and PatIndex( @String, sc.Text) > 0
Order By so.Name

Select @RecCount = @@RowCount


If @RecCount > 0
Begin
	Select @Message=''* ''+ convert(varchar(8),@RecCount) + '' Stored procedure(s) found in  database ? having the string "'' + @String + ''" *''
	Select @ul = Replicate(''*'', DataLength(@Message))

	PRINT @ul
	PRINT @Message
	Print @ul
End'

tested OK..



Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[TysonLPrice]

Beautiful! Thanks!

 

[SajidAttar]

Glad to help..


Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell