T-SQL Tips

t-sql-tips.md

T-SQL Tips

Get definitions for all stored procedures and functions for a database

Getting the definitions can be useful when attempting to locate specific constants or dynamic usage of other database objects such as functions or tables.

select p.[name], s.[definition]
from sys.procedures p
   inner join sys.sql_modules s on p.[object_id] = s.[object_id]
order by p.[name]

Get definitions for all triggers for a database

select p.[name], s.[definition]
from sys.triggers p
   inner join sys.sql_modules s on p.[object_id] = s.[object_id]
order by p.[name]

Enable xact_abort by default for entire database

EXEC sp_configure 'user options', 16384
reconfigure

To test whether this option is already set use @@options. Note that this will be true whether the above database wide defaults setting is used or xact_abort is set using set xact_abort on at the session level.

if ( (16384 & @@options) = 16384 ) printt 'XACT_ABORT'

Additionally other options can also be set at the database level.