SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.



Posted by Steve Jones on 20 May 2011

This is a nice short one that I should keep around. Would be handy to get a quick look at the server for things that I normally miss.

Posted by Eric Willemstein on 27 May 2011

This is good to have in the tool box, it's fast and clean.

Leave a Comment

Please register or log in to leave a comment.