This is Syed Aslam Basha here from Information security and risk management team.

For one of my projects I had to validate the changes in some 50 stored procedures (SP). You can do this in;

    • Connect to DB, Click on your Database (DB), programmability –> stored procedures, right click on the each SP and click on modify to open and validate it
    • Easy way is to, right click on your database, click on tasks, click on generate scripts follow through the script wizard and generate the scripts to a file
    • One more way is by coding
      • sp_helptext “stored procedure name” it prints one SP code
      • The following code lists out code for all the SPs in a DB 
 1: use master
 2: GO 
 3: drop table #temp1
 4: create table #temp1 (id int identity,name varchar(128))
 5: USE YourDBname
 6: GO 
 7: insert into #temp1 select name from sysobjects where xtype='p'
 8: go
 9: declare @var int
 10: declare @sp_name varchar(128)
 11: declare @cmd varchar(128)
 12: set @var = 1
 13: while @var <= (select count (1) from #temp1)
 14: begin
 15: select @sp_name = name from #temp1 where id = @var
 16: set @cmd = 'sp_helptext ' + @sp_name
 17: exec (@cmd)
 18: set @var = @var+1
 19: end
 20: go

 

- Syed Aslam Basha (syedab@microsoft.com)

Microsoft Information Security Tools (IST) Team

Test Lead

---------------------------------------------------------

Please leave a comment if the blog post has helped you.