THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Quickly Find Your DDL Triggers with PowerShell

As a consultant I spend much of the first day or two at a client site discovering everything I can about their servers, before I start planning the engagement. You can find DDL triggers by going into the Object Explorer in Management Studio, open up each database, select Programmability, then expand the Database Triggers folder, but if there are a lot of databases, this can get tedious. Just a few lines of PowerShell script will return a quick list of the names of all DDL triggers by database.

First we load the SMO library and connect to the server.

[System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | out-null

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next, return the list of server-level triggers.

# Return the Server-level DDL Triggers
$s.Triggers | select Name, TextHeader | format-list

Then, iterate through the databases, returning the database name and DDL triggers in each.

# Iterate through the databases to check database names
foreach ($db in $s.Databases) {
	[string]$nm = $db.Name
	$db.Triggers | select @{Name="Database"; Expression = {$nm}}, Name, TextHeader | format-list

If the script doesn't return anything, I don't have to investigate further, but if it does, I know the name and host database of all the DDL triggers on the server.


Published Friday, May 20, 2011 9:55 AM by AllenMWhite
Filed under: ,



GrumpyOldDBA said:

thanks, very useful - just decided I should make use of powershell so I can make this the first entries to my "useful scripts"

May 20, 2011 9:17 AM

AaronBertrand said:

Here's my T-SQL version. :-)


INSERT #t SELECT N'---server---', name FROM master.sys.server_triggers;

EXEC master.dbo.sp_MSForEachDB 'INSERT #t SELECT N''?'',name

FROM [?].sys.triggers WHERE parent_class = 0';


May 20, 2011 9:44 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement