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

Hi 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.

Show quoteHide quote

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:
Show quoteHide quote

What is this called, and how to do it?
SQL Server vs the Competition
Date Only Data Type
Time Calculation - NEED HELP PLEASE!
Array?