Printing stored procedure

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.