Sorting stored procedures by Create Date in Enterprise Manager may not work! (SQL Server 2000)

Here's a puzzler: Open up Enterprise Manager (EM), drill down to a database on your SQL Server, and select the Tables node. Now, in the right panel, try sorting the list of tables in ascending and descending order. Works just fine, right? But now select the Stored Procedures node and try the same thing. Look closely.

If your system is like ours, it simply doesn't work at all! Despite the fact that EM claims to be sorting by the Create Date column, we noticed that the dates are all out of order! And the same is true for Views. And User Defined Functions. And Rules. In fact, the only collection we saw sorting correctly was Tables. And it gets weirder: Continue clicking the column header and you'll notice that the sort order is *different* every time! EM is basically just randomizing the list (or at least, it looks random to those of us whose native language isn't binary).

If you encounter this same mystery and you need to sort your stored procedures by their creation date, there's another way: using Query Analyzer. The following query will accomplish the task:

SELECT *

FROM sysobjects

WHERE xtype = 'P' /* Change the xtype for other database objects */

ORDER BY create DESC