Query to count triggers per table

[Chris12358]

I'm trying to write a query that will query the sysobjects table of SQL server 6.5 on NT, and tell me which tables (type - U) do not have three required triggers attached to them. If anyone could help me out with this, ie, where to find an example, etc, I would appreciate it. So far I've figured out how to display there the type is U(table) or TR(trigger), but I cant figure out how to count triggers per table. This is an excerpt from the result set I get currently:<br>
<br>
U T_LOCATION_DATA T_LOCATION_DATA <br>
U T_LOGON T_LOGON <br>
TR T_LOGON tD_T_LOGON <br>
TR T_LOGON tI_T_LOGON <br>
TR T_LOGON tU_T_LOGON <br>
<br>
As you can see, there are three triggers associated with T_LOGON, but none associated with T_LOCATION_DATA. All I want displayed is T_LOCATION_DATA, because it is missing the triggers. Anyone's help would be appreciated, Thanks.

 

[carp]

Chris -<br>
I'm not familiar with SQLServer, but if we can agree to call the table you are getting this information from THE_TABLE with columns OBJ_TYPE, TABLE_NAME, and OBJ_NAME, <br>
your example would look like:<br>
<br>
OBJ_TYPE TABLE_NAME OBJ_NAME<br>
U T_LOCATION_DATA T_LOCATION_DATA <br>
U T_LOGON T_LOGON<br>
TR T_LOGON tD_T_LOGON<br>
TR T_LOGON tI_T_LOGON<br>
TR T_LOGON tU_T_LOGON <br>
<br>
<br>
SELECT table_name<br>
FROM the_table<br>
WHERE obj_type = 'U'<br>
AND table_name NOT IN (SELECT table_name<br>
FROM the_table<br>
WHERE obj_type = 'TR');<br>
<br>
Another possibility would be <br>
<br>
SELECT table_name, count(*)<br>
FROM the_table<br>
GROUP BY table_name<br>
HAVING count(*) = 1;<br>
<br>