How do I count the number of objects in SQL Server 2000 and get its modified date?

I need to count the number of objects in SQL Server 2000 when restoring from the database to make sure that restore includes the latest updates. I also wanted to get the latest date an object was created or modified.

Specifically wanted to get counts for number of tables, the number of views, the number of udfs, the number of sprocs, and the date it was created or modified.

select 
    count(xtype) as [MyCounts], 
    crdate as [CreateDate], 
    refdate as [ModifiedDate] 
from sysobjects 
where xtype like 'U%'  
--does not appear to be working correctly.

Solution

A very basic solution would simply use grouping and aggregating, like this:

SELECT
  xtype,
  total_count  = COUNT(*),
  last_crdate  = MAX(crdate),
  last_refdate = MAX(refdate)
FROM sysobjects
GROUP BY xtype

This, however, returns information on all types of objects in the current database, including those you didn't mention in your question, like constraints, keys etc.

So you might want to narrow the resulting list by applying a filter on xtype, like this:

SELECT
  xtype,
  total_count  = COUNT(*),
  last_crdate  = MAX(crdate),
  last_refdate = MAX(refdate)
FROM sysobjects
WHERE xtype IN ('U', 'V', 'FN', 'TF', 'IF', 'P')
GROUP BY xtype

Note that there are three types of UDFs in SQL Server. They are designated in sysobjects as follows:

FN – scalar function
TF – multi-statement table-valued function
IF – inline table-valued function

Accordingly the information about functions will be scattered in three rows if you use the above script. If you'd like to group those results in one row, your query would have to be slightly more sophisticated. For example, like this:

SELECT
  type,
  total_count  = COUNT(*),
  last_crdate  = MAX(crdate),
  last_refdate = MAX(refdate)
FROM (
  SELECT
    type = CASE
      WHEN xtype = 'U' THEN 'table'
      WHEN xtype = 'V' THEN 'view'
      WHEN xtype = 'P' THEN 'proc'
      WHEN xtype IN ('FN', 'TF', 'IF') THEN 'udf'
    END,
    crdate,
    refdate
  FROM sysobjects
  WHERE xtype IN ('FN', 'TF', 'IF', 'P', 'U', 'V')
) s
GROUP BY type

Here the original types are first replaced by custom types based on the xtype value. All rows pertaining to functions are marked simply as udf, regardless of the actual function type, so in the end you can simply group by the custom type column and get the necessary totals, the information on functions now being gathered in one row.

Reference:

sys.sysobjects (Transact-SQL)