List All Stored Procedure Modified in Last N Days

 If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT nameFROM sys.objectsWHERE type = 'P'AND DATEDIFF(D,modify_date, GETDATE()) < 7----Change 7 to any other day value

Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT nameFROM sys.objectsWHERE type = 'P'AND DATEDIFF(D,create_date, GETDATE()) < 7----Change 7 to any other day value.

Date condition in above script can be adjusted to retrieve required data.and the other types are:

Object type: AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure


To list out all the month in a year in Sql Server.

select datename(month,dates) as month_name from
(
select dateadd(month,number,0) as dates from master..spt_values where type=’p’ and number between 0 and 11
) as t