How to check stored procedure was last modified in sql server 2000

  • HI,

    How to check last modified partiulare stored procdure?

    Any t-sql query ..?

    Please anyone help me?

  • select SPECIFIC_NAME ,LAST_ALTERED from information_schema.routines

    where SPECIFIC_NAME = procname

    Jayanth Kurup[/url]

  • Hi,

    Its works. Great

    Thanks lot....

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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[/url]

  • 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[/url]

  • 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?

  • SELECT name, create_date, modify_date

    FROM sys.objects where name='procedurename'

  • 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

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply