August 9, 2011 at 3:08 am
HI,
How to check last modified partiulare stored procdure?
Any t-sql query ..?
Please anyone help me?
August 9, 2011 at 3:12 am
August 9, 2011 at 3:23 am
Hi,
Its works. Great
Thanks lot....
August 9, 2011 at 5:47 am
solomon.jernas (8/9/2011)
Hi,Its works. Great
Thanks lot....
NO! It doesn't work. Despite the column heading of LAST_ALTERED, SQL Server 2000 had no mechanism for storing the date a stored procedure, view, or function was altered. Prove it to yourself... generate the code from master.INFORMATION_SCHEMA.ROUTINES and look at how the data for the LAST_ALTERED column is generated.
I'll save you the time...
create view INFORMATION_SCHEMA.ROUTINES
as
SELECT
SPECIFIC_CATALOG= db_name(),
SPECIFIC_SCHEMA= user_name(o.uid),
SPECIFIC_NAME= o.name,
ROUTINE_CATALOG= db_name(),
ROUTINE_SCHEMA= user_name(o.uid),
ROUTINE_NAME= o.name,
ROUTINE_TYPE= convert(nvarchar(20), CASE
WHEN o.xtype='P' THEN 'PROCEDURE'
ELSE 'FUNCTION' END),
MODULE_CATALOG= convert(sysname,null),
MODULE_SCHEMA= convert(sysname,null),
MODULE_NAME= convert(sysname,null),
UDT_CATALOG= convert(sysname,null),
UDT_SCHEMA= convert(sysname,null),
UDT_NAME= convert(sysname,null),
DATA_TYPE= case when o.xtype IN ('TF', 'IF') then N'TABLE' else spt_dtp.LOCAL_TYPE_NAME end,
CHARACTER_MAXIMUM_LENGTH= convert(int, OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin),
CHARACTER_OCTET_LENGTH= convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in ('nchar', 'nvarchar', 'ntext')
then 2*OdbcPrec(c.xtype, c.length, c.xprec)
else OdbcPrec(c.xtype, c.length, c.xprec)
end),
COLLATION_CATALOG= convert(sysname, null),
COLLATION_SCHEMA= convert(sysname, null),
COLLATION_NAME= c.collation,
CHARACTER_SET_CATALOG= convert(sysname, null),
CHARACTER_SET_SCHEMA= convert(sysname, null),
CHARACTER_SET_NAME= convert(sysname, case
when spt_dtp.LOCAL_TYPE_NAME in ('char', 'varchar', 'text')
then a_cha.name
when spt_dtp.LOCAL_TYPE_NAME in ('nchar', 'nvarchar', 'ntext')
then N'Unicode'
else NULL
end),
NUMERIC_PRECISION= c.xprec,
NUMERIC_PRECISION_RADIX= spt_dtp.RADIX,
NUMERIC_SCALE= c.scale,
DATETIME_PRECISION= spt_dtp.SQL_DATETIME_SUB,
INTERVAL_TYPE= convert(nvarchar(30),null),
INTERVAL_PRECISION= convert(smallint,null),
TYPE_UDT_CATALOG= convert(sysname,null),
TYPE_UDT_SCHEMA= convert(sysname,null),
TYPE_UDT_NAME = convert(sysname,null),
SCOPE_CATALOG = convert(sysname,null),
SCOPE_SCHEMA = convert(sysname,null),
SCOPE_NAME= convert(sysname,null),
MAXIMUM_CARDINALITY= convert(bigint,null),
DTD_IDENTIFIER= convert(sysname,null),
ROUTINE_BODY= convert(nvarchar(30), 'SQL'),
ROUTINE_DEFINITION= convert(nvarchar(4000),
(SELECT TOP 1 CASE WHEN encrypted = 1 THEN NULL ELSE com.text END
FROM syscomments com WHERE com.id=o.id AND com.number<=1 AND com.colid = 1)),
EXTERNAL_NAME= convert(sysname,null),
EXTERNAL_LANGUAGE= convert(nvarchar(30),null),
PARAMETER_STYLE= convert(nvarchar(30),null),
IS_DETERMINISTIC= convert(nvarchar(10),
CASE WHEN ObjectProperty(o.id, 'IsDeterministic')=1
THEN 'YES' ELSE 'NO' END),
SQL_DATA_ACCESS= convert(nvarchar(30), CASE
WHEN o.xtype='P' THEN 'MODIFIES'
ELSE 'READS' END),
IS_NULL_CALL= convert(nvarchar(10),null),
SQL_PATH= convert(sysname,null),
SCHEMA_LEVEL_ROUTINE= convert(nvarchar(10),'YES'),
MAX_DYNAMIC_RESULT_SETS= convert(smallint, CASE
WHEN o.xtype='P' THEN -1 ELSE 0 END),
IS_USER_DEFINED_CAST= convert(nvarchar(10),'NO'),
IS_IMPLICITLY_INVOCABLE= convert(nvarchar(10),'NO'),
[font="Arial Black"]CREATED= o.crdate,
LAST_ALTERED= o.crdate[/font]
FROM
sysobjects o LEFT OUTER JOIN
(syscolumns c JOIN master.dbo.spt_datatype_info spt_dtp
ON c.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)
)
ON (o.id = c.id AND c.number = 0 AND c.colid = 0),
master.dbo.syscharsetsa_cha --charset/1001, not sortorder.
where
o.xtype IN ('P','FN','TF', 'IF')
AND permissions(o.id) != 0
ANDa_cha.id = isnull(convert(tinyint, CollationProperty(c.collation, 'sqlcharset')),
convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2011 at 1:30 pm
Hi Jeff
I looked up the def for routines view in SQL 2005 and here' what i found ,
o.create_date AS CREATED,
o.modify_date AS LAST_ALTERED
FROM
sys.objects$ o LEFT JOIN sys.parameters c
ON (c.object_id = o.object_id AND c.parameter_id = 0)
I tried it out an it worked fine. Could you provide an example where this would fail ?
August 9, 2011 at 1:32 pm
August 9, 2011 at 1:45 pm
solomon.jernas (8/9/2011)
HI,How to check last modified partiulare stored procdure?
Any t-sql query ..?
Please anyone help me?
Check the history in your source control system?
November 15, 2012 at 4:21 am
SELECT name, create_date, modify_date
FROM sys.objects where name='procedurename'
November 15, 2012 at 4:28 am
Year old thread, and the title stated SQL 2000 (where sys.objects did not exist)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply