Last modified Db User Objects & Tables without Trigger in Sql server

If you need to check the details of all DB User Objects by Last Modified Date try the following query


select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc  
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.
 
SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name