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.

Allen