Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to check stored procedure was last modified in sql server 2000 Expand / Collapse
Author
Message
Posted Tuesday, August 9, 2011 3:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:54 PM
Points: 150, Visits: 807
HI,

How to check last modified partiulare stored procdure?

Any t-sql query ..?


Please anyone help me?



Post #1156606
Posted Tuesday, August 9, 2011 3:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
select SPECIFIC_NAME ,LAST_ALTERED from information_schema.routines
where SPECIFIC_NAME = procname


Jayanth Kurup
Post #1156609
Posted Tuesday, August 9, 2011 3:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:54 PM
Points: 150, Visits: 807
Hi,

Its works. Great



Thanks lot....



Post #1156623
Posted Tuesday, August 9, 2011 5:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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'),
CREATED = o.crdate,
LAST_ALTERED = o.crdate

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.syscharsets a_cha --charset/1001, not sortorder.
where
o.xtype IN ('P','FN','TF', 'IF')
AND permissions(o.id) != 0
AND a_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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1156726
Posted Tuesday, August 9, 2011 1:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014

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 ?


Jayanth Kurup
Post #1157215
Posted Tuesday, August 9, 2011 1:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
Aah i see where the problem is , the post mentions 2000 in the header. I went by the Post category being SQL Server 2008.

Jayanth Kurup
Post #1157219
Posted Tuesday, August 9, 2011 1:45 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:20 PM
Points: 3,122, Visits: 11,401
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?

Post #1157237
Posted Thursday, November 15, 2012 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 11:46 PM
Points: 1, Visits: 50
SELECT name, create_date, modify_date
FROM sys.objects where name='procedurename'
Post #1385080
Posted Thursday, November 15, 2012 4:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 42,458, Visits: 35,518
Year old thread, and the title stated SQL 2000 (where sys.objects did not exist)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1385081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse