String search in Stored Procedures

I needed a way to get a list of all stored procedure that contained a TRUNCATE command. We were performing a security audit on one of our applications, trying to determine which users needed what access.

I stumbled on this article¬† from Pinal Dave’s blog and ended running this query:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%TRUNCATE%’

Works like a charm. Also used it to find Stored procedures with DROP

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
where sc.TEXT LIKE ‘%DROP%’

Advertisements

OSQL dump to Text

At times you need to dump the results of a stored procedure call to a text file. To me the simplest way to achieve this is a call to the OSQL command line utility.

osql -E /Q “exec master..sp_help_revlogins” -o c:\GenLogins.sql

This little gem will use the sp_help_revlogins (More info here) to dump the SQL logins to a text file. Handy to run for recovery purposes. The resulting script can be used to quickly recreate logins after a restore.

Simple Stored Procedure with Output Parameter

While working on new project at work this week, I have created a set of five stored procedures to accomplish various tasks. I thought I would share a quick example of a stored procedure that returned a success flag.

create procedure sp_procedure1
@id int,
@success int OUTPUT
AS

update table1 set col1 = ‘New Value’ where id = @id
IF @@ROWCOUNT = 0
SELECT @success = 0
ELSE
SELECT @success = 1
GO

Then to call the stored procedure you would do something like this

DECLARE @success int
exec sp_procedure1 1, @success OUTPUT
select @success

This call will execute your stored procedure. If a row is updated then you will receive a success flag of 1 back from the sp. If zero rows are updated then you will receive a success flag of 0 back from the sp.

Before working with the ouptput parameters extensivley this week, I found myself constantly hitting BOL to look this concept up. Now I can just hit this awesome blog instead if I need a quick refresher, assuming I remember to look at this blog….