need sql script to show object counts

[Tison]

I need a scripts/procedure that will show the following ;
Alias ; 0
Table ; 10
Index ; 4
Constraint ; 9
etc for ALL the object types.
(Selecting from sysobjects does not give all the info)

 

[mudharan]

You can use the sql query below and instead of else you can add when conditions to get your count for the rest of object types.


SELECT CASE
WHEN type = 'P' Then 'Procedures'
WHEN type = 'U' Then 'User Tables'
WHEN type = 'S' Then 'System Tables'
WHEN type = 'V' Then 'Views'
WHEN type = 'TR' Then 'Triggers'
WHEN type = 'XP' Then 'Extended Stored Procedures'
WHEN type = 'RI' Then 'Refrential Constraint'
ELSE 'Others'
END
AS Objects,
COUNT(type) AS Occurs
FROM sysobjects GROUP BY type


Hope this answered your question.

Murali

 

[Tison]

N,,,this does not give me the other objects like indexes,group,etc