Retrieve list of tables without triggers

I need to do some db cleaning, how can I get a list of tables within the
database that do not have any triggers.
Thanks in advanceHi scuba79,
Try the following on your database:
select name from sysobjects
where xtype = 'U'
and id not in
(
select parent_obj from sysobjects
where xtype = 'TR'
)
xtype = 'U' will return all the user tables within your database
xtype = 'TR' are trigger objects.
Every trigger belongs to a table/parent and the parent object id is store in
sysobjects.parent_obj. By using this info we can filter out all the user
tables that have triggers.
Hope this help.
"scuba79" wrote:

> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance|||Here is one way
SELECT * FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
http://sqlservercode.blogspot.com/|||Little correction I forgot to filter out the views
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
http://sqlservercode.blogspot.com/|||assuming sql2000:
select user_name(uid) as table_schema, name as table_name
from sysobjects so
where xtype='U'
and objectproperty(id,'IsMSShipped')=0
and not exists (
select *
from sysobjects
where xtype='TR'
and parent_obj = so.id
)
order by table_name
scuba79 wrote:
> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance