InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:56:54 GMT20http://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxYear old thread, and the title stated SQL 2000 (where sys.objects did not exist)Thu, 15 Nov 2012 04:28:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxSELECT name, create_date, modify_dateFROM sys.objects where name='procedurename'Thu, 15 Nov 2012 04:21:19 GMTpandaamit04http://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspx[quote][b]solomon.jernas (8/9/2011)[/b][hr]HI,How to check last modified partiulare stored procdure?Any t-sql query ..?Please anyone help me?[/quote]Check the history in your source control system?Tue, 09 Aug 2011 13:45:07 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxAah i see where the problem is , the post mentions 2000 in the header. I went by the Post category being SQL Server 2008.Tue, 09 Aug 2011 13:32:20 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxHi Jeff I looked up the def for routines view in SQL 2005 and here' what i found ,[code="sql"] 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) [/code]I tried it out an it worked fine. Could you provide an example where this would fail ?Tue, 09 Aug 2011 13:30:43 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspx[quote][b]solomon.jernas (8/9/2011)[/b][hr]Hi,Its works. Great Thanks lot....[/quote]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...[code="sql"]create view INFORMATION_SCHEMA.ROUTINES asSELECT 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.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[/code]Tue, 09 Aug 2011 05:47:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxHi,Its works. Great Thanks lot....Tue, 09 Aug 2011 03:23:28 GMTsolomon.jernashttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxselect SPECIFIC_NAME ,LAST_ALTERED from information_schema.routineswhere SPECIFIC_NAME = procnameTue, 09 Aug 2011 03:12:04 GMTJayanth_Kuruphttp://www.sqlservercentral.com/Forums/Topic1156606-1549-1.aspxHI,How to check last modified partiulare stored procdure?Any t-sql query ..?Please anyone help me?Tue, 09 Aug 2011 03:08:15 GMTsolomon.jernas