Ordering Table creation dates in SQL Enterprise Manager

[StevenK]

This issue is something that may well easily be resolved. When I use SQL Server Enterprise Manager and open a database listing all the tables associated I expected that by clicking the 'Create Date' at the top of the column I would think that the tables would be ordered accordingly - i.e. the most recently created table at the top of the list (or bottom accordingly).
This is clearly not the case - I am seeing the ordering of the list according to the day part of the date (i.e. those dates with 31 come first, then 30, etc) - so I may see the order of tables with Create Dates like '31/10/98', '30/09/01', '28/06/00', etc. This is clearly misleading for me.
Is there a method of displaying these tables in the order of creation so I know what new tables have been added since a given date. Is it as simple as just indicating the date format - such that the year takes precedence over the day element of the date ?
It could well be something that I've overlooked.
Any help would be appreciated.
Thanks in advance.
Steve

 

[tlbroadbent]

Unfortunately, SQL 7 Enterprise Manager (base version and SP1) seems to sort the dates as strings or character data types rather than date. This has been corrected in SQL 2000. I don't know if it was corrected in SQL 7 SP2 or SP3.

You can query sysobjects and order by crdate. Though that isn't much help when working in Enterprise Manager. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

Unfortuantly still not corrected in SQL 7.0 SP2 or SP3.

As Terry mentioned the only thing you can do is use a query such as:

select convert(varchar(10),crdate,103) as Creation_Date,
name as Table_Name
from sysobjects where type = 'U'
order by crdate desc


Rick.

 

[StevenK]

Thanks for the query - this should allow me to display the table names as required.
It does explain why I wasn't able to see how to order them correctly if the option is not there.
Thanks for the pointers.
Steve