Post reply

Find DDL time

  • November 21, 2008 at 12:26 pm

    #128838

    Hi all,

    Below is the query I'm using to find the status of the objects,

    select name,status,refdate from sysobjects

    I know the refdate in this table is the date the object was created. But is it possible to find out the last DDL time of the object in a query??

    Thanks a lot.

  • November 21, 2008 at 12:34 pm

    #901844

    psangeetha (11/21/2008)

    Hi all,

    Below is the query I'm using to find the status of the objects,

    select name,status,refdate from sysobjects

    I know the refdate in this table is the date the object was created. But is it possible to find out the last DDL time of the object in a query??

    Thanks a lot.

    You may want to look at sys.objects instead of sysobjects. In sys.objects there is both a create_date and a modify_date (date an alter was executed). Unfortunately, there is no delete_date, as it just goes away when deleted.

  • November 21, 2008 at 1:12 pm

    #901861

    Thanks. But the sys.objects does not have status column in it?

  • November 21, 2008 at 2:55 pm

    #901895

    Maybe not, but sysobjects is for backward compatibility with earlier versions of SQL Server and could easily go away in future versions of SQL Server. I think I read a post that they are no longer available in SQL Server 2008, but I haven't verified that for myself yet, as I haven't purchased SQL Server 2008 Developer Edition yet.

  • November 21, 2008 at 2:58 pm

    #901898

    Also, from Books Online regarding sys.sysobjects:

    Important:

    If you use any of the following SQL Server 2005 DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects.

    CREATE | ALTER | DROP USER

    CREATE | ALTER | DROP ROLE

    CREATE | ALTER | DROP APPLICATION ROLE

    CREATE SCHEMA

    ALTER AUTHORIZATION ON OBJECT

Viewing 5 posts - 1 through 4 (of 4 total)

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