VB.Net how to export codes of or exact Stored Procedures of MSSQL 2008

Question

Is it possible to get that whole stored procedure codes and save it to a text file?

• I would like to create a program in VB.net where the user will just have to click a button then save/extract all the procedures that have been altered or newly created on that day for backup purposes.

• I can already check those dates using this query..

  SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P'
  

• I would like to get the whole codes.. not the result of that stored procedure.

• I know it can be done in mssql tasks but I want the other way around..

Answer 1

Use OBJECT_DEFINITION function:

SELECT name, OBJECT_DEFINITION(object_id)
FROM sys.procedures 

btw, a simple google search (get stored procedure text sql server) got me this link. You should have googled first.

Answer 2

for getting modified list
SELECT 
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE create_date = dynamicdate()

to get text of stored preocedure

SELECT text FROM syscomments WHERE id = (SELECT id FROM sysobjects WHERE name = 'spname'

and iterate the first result then pass sp name to second script