CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Out
Out

Reputation: 675

How to generate script of a stored procedure based on modified date?

I have 50 stored procedures and in those I have modified only 3 today, I just want to generate scripts for those 3 modified stored procedures.

How can I achieve this according to modified date??? Anyone please help

Upvotes: 1

Views: 2692

Answers (3)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Try this

   SELECT name , create_date, modify_date ,sm.definition FROM sys.procedures sp
   Inner join sys.sql_modules sm  ON sp.object_id = sm.object_id 
   WHERE CONVERT(DATE,sp.modify_date)    = CONVERT(DATE, '02/19/2013')

Upvotes: 2

Frederic
Frederic

Reputation: 1028

something like this?

select p.name
  ,stuff(m.definition,CHARINDEX('create',m.definition,0),6,'alter') as definition
from sys.procedures p
inner join sys.sql_modules m
on m.object_id = p.object_id
where modify_date >= cast(GETDATE() as date)

the resultset is like

name          definition
sp_xxxxxx     alter PROCEDURE [dbo].[sp_xxxxxx] ...

Upvotes: 0

Will A
Will A

Reputation: 24988

Try this - run in SSMS with "Results to Text" to preserve tabs etc.

SELECT      M.definition
FROM        sys.procedures P WITH(NOLOCK)
INNER JOIN  sys.sql_modules M WITH(NOLOCK)
ON          M.object_id = P.object_id
WHERE       P.modify_date >= CAST(GETDATE() AS DATE) -- Modified since midnight today.

Upvotes: 1

Related Questions