Thursday, April 13, 2006

Tables with computed columns

Computed columns some times causes re-indexing of database to fail. To find out which tables got computed columns, I normally use the following query.

select so.id as ObjectId,
so.name as ObjectName,
sc.name as ComputedColumnName
FROM sysobjects so, syscolumns sc
where so.id = sc.id
and sc.iscomputed = 1
and so.xtype = 'u'

Wednesday, April 12, 2006

Number of Triggers

This script is used to find the number of triggers on database tables in database.

select object_name(parent_obj) as ObjectName,
count(name) as TotalTriggers
from sysobjects where xtype = 'tr'group by object_name(parent_obj)order by count(name) desc