get change date of sql server objects

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get change date of sql server objects

Author

Topic

gridview
Starting Member

11 Posts

Posted - 2008-12-07 : 14:12:40

For frequent migration to different environment we have to provide scripts for any database changes, most of the time developer forget to send few of the scripts which cause error in the other environments.
I am using SQL server 2005.

I was looking for any sql query which can list the create date and modify date of table, stored procedure, views etc in a database.

I google and found the below query but I get the below error message:
" Invalid object name 'sys.objects'."

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

Note: I do not have access to Master database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-07 : 15:01:08

What version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:51:02

you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out date created and last modified dates of procedures & functions. something like

SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:52:01

for tables and views try this also


SELECT name
FROM sysobjects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

gridview
Starting Member

11 Posts

Posted - 2008-12-07 : 21:27:25

I am using SQL Server 2005

quote:
Originally posted by tkizer

What version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gridview
Starting Member

11 Posts

Posted - 2008-12-07 : 21:28:50

I have tried the query posted by you but i get the following error message:

Invalid column name 'modify_date'.

quote:
Originally posted by visakh16

for tables and views try this also

SELECT name
FROM sysobjects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

gridview
Starting Member

11 Posts

Posted - 2008-12-07 : 21:30:41

How this will help me to get the createDate and modifiedDate of a particular database on the development SQL server?

please explain, Thanks.

quote:
Originally posted by visakh16

you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out date created and last modified dates of procedures & functions. something like

SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 22:22:09

quote:
Originally posted by gridview

How this will help me to get the createDate and modifiedDate of a particular database on the development SQL server?

please explain, Thanks.

quote:
Originally posted by visakh16

you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out date created and last modified dates of procedures & functions. something like

SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

but you asked created & modified date of procedures in your first post. The above query gives you created modified dates for procedures/functions in db.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 22:34:15

quote:
Originally posted by gridview

I am using SQL Server 2005

quote:
Originally posted by tkizer

What version of SQL Server are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

whats the compatibility level of db? run this & see

Exec sp_dbcmptlevel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources