System Table Queries (2000)

-- 1) Transact-SQL script to report which columns are identity columns:
SELECT O.name, C.name from sysobjects O
INNER JOIN syscolumns C on O.id = C.id
WHERE o.type='U' AND objectproperty (o.id, 'TABLEHASIDENTITY') = 1
AND columnproperty (o.id, c.name, 'IsIdentity') = 1
ORDER BY O.name, C.name

-- 2) Transact-SQL script to report which tables have triggers
SELECT O.name from sysobjects O
WHERE
o.type='U' AND (objectproperty (o.id, 'TableHasDeleteTrigger') = 1
OR objectproperty (o.id, 'TableHasInsertTrigger') = 1
OR objectproperty (o.id, 'TableHasUpdateTrigger') = 1)
ORDER BY O.name

-- 3) Transact-SQL script to report which tables do not have primary keys
SELECT O.name from sysobjects OWHERE o.type='U' AND objectproperty (o.id, 'TableHasPrimaryKey') = 0ORDER BY O.name

--4) Transact-SQL script to report which tables have identity columns or time stamps:
SELECT O.name from sysobjects O
WHERE o.type='U' AND (objectproperty (o.id, 'TableHasIdentity') = 1
OR objectproperty (o.id, 'TableHasTimestamp') = 1)
ORDER BY O.name