SQL Server – get all triggers

Here is an often used SQL statement to get a list of Triggers, in SQL Server. I’m posting this here for my own convenience and perhaps someone else will benefit.

SELECT
  [so].[name] AS sys_obj,
  so.type,
  USER_NAME([so].[uid]) AS obj_owner,
  USER_NAME([so2].[uid]) AS table_schema,
  OBJECT_NAME([so].[parent_obj]) AS table_name,
  OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
  OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
  OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
  OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
  OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
  OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] 
 FROM sysobjects AS [so]
 INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
 WHERE [so].[type] = 'TR';

 

 


					

Kettle PDI, Named Parameters, and Generic Database Connections

Kettle / PDI Generic SQL Server JTDS database with parameters and a Generic SQL Server Database Connection

The purpose of using named parameters is to abstract out what parts of a database connection change, from one environment to another, and make that part of a configuration that can be maintained elsewhere.  For example,  for a given ETL to extract Appointment data, it might need to connect to different database servers, databases, and use different credentials in Development, QA, and Production.

This example makes the database connection more generic than necessary for that purpose, just as a learning exercise.

First… Define a database connection and use replacement variables for all of the parameters. In this example, the following parameters are used.

  • DB_HOST
  • DB_DBNAME
  • DB_PORT
  • DB_USER
  • DB_PW

Continue reading