are you sql god?

Hi all,

I need the time that a stored procedure was created/altered.

In sysobjects table, creation date of the stored procedure is logged. But if the stored procedure is altered this column is not updated.

1. Is last alter date of a stored procedure loged somewhere?

2. If not, can I restrict users to "alter procedure" but not "drop/create procedure" so that they will have to drop and re-create procedures so sysobjects.crdate will always be updated.

I hope I will get a positive answer.

Ata

Besides the create_date, there is also a modify_date in the objects catalog. That is probably what you are looking for.

Thanks
Laurentiu

|||

If this is SQL Server 2005, you should query the DMV... sys.objects

This is taken from the Books-on-line (the sys.objects DMV.)

Column name	Data type	Description
name	sysname	Object name.
object_id	int	Object identification number. Is unique within a database.
schema_id	int	ID of the schema that the object is contained in. For all schema-scoped system objects that ship with SQL Server 2005, this value will always be IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA').
principal_id	int	ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner. Is NULL if the object type is one of the following: C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) TA = Assembly (CLR-integration) trigger TR = SQL trigger UQ = UNIQUE constraint
parent_object_id	int	ID of the object to which this object belongs. 0 = Not a child object.
type	char(2)	Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint P = SQL stored procedure PC = Assembly (CLR) stored procedure FN = SQL scalar function FS = Assembly (CLR) scalar function FT = Assembly (CLR) table-valued function R = Rule (old-style, stand-alone) RF = Replication-filter-procedure SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TR = SQL DML trigger IF = SQL inlined table-valued function TF = SQL table-valued-function U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure IT = Internal table
type_desc	nvarchar(60)	Description of the object type. AGGREGATE_FUNCTION CHECK_CONSTRAINT DEFAULT_CONSTRAINT FOREIGN_KEY_CONSTRAINT PRIMARY_KEY_CONSTRAINT SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE SQL_SCALAR_FUNCTION CLR_SCALAR_FUNCTION CLR_TABLE_VALUED_FUNCTION RULE REPLICATION_FILTER_PROCEDURE SYNONYM SERVICE_QUEUE CLR_TRIGGER SQL_TRIGGER SQL_INLINE_TABLE_VALUED_FUNCTION SQL_TABLE_VALUED_FUNCTION USER_TABLE UNIQUE_CONSTRAINT VIEW EXTENDED_STORED_PROCEDURE INTERNAL_TABLE
create_date	datetime	Date the object was created.
modify_date	datetime	Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shipped	bit	Object is created by an internal SQL Server component.
is_published	bit	Object is published.
is_schema_published	bit	Only the schema of the object is published.