THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

Allen

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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. :-)

CREATE TABLE #t(d SYSNAME, n SYSNAME);

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';

SELECT d,n FROM #t;

May 20, 2011 9:44 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement