SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
solomon.jernas
solomon.jernas
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 979
HI,

How to check last modified partiulare stored procdure?

Any t-sql query ..?


Please anyone help me?
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2913 Visits: 1351
select SPECIFIC_NAME ,LAST_ALTERED from information_schema.routines
where SPECIFIC_NAME = procname

Jayanth Kurup
solomon.jernas
solomon.jernas
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 979
Hi,

Its works. Great



Thanks lot....
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86037 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2913 Visits: 1351
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
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2913 Visits: 1351
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
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 11771
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?
pandaamit04
pandaamit04
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 135
SELECT name, create_date, modify_date
FROM sys.objects where name='procedurename'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87255 Visits: 45272
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search