steve gray 8/9/2011 7:24:59 AM

Send the Contents of all the Stored Procedures, Triggers, and Functions to a Text File

This article, along with other great articles, can be found on our SSRS General Index Menu.  

This script will send the contents of all the Stored Procedures, Functions, and Triggers in a database to a text file. In fact, it will send any object that can be found in SELECT * FROM SYSOBJECTS

Why is that useful? For me, it's indispensible. The Dynamics GP database (Microsoft's accounting package) that I work in has north of 34000 objects. If I change something, or need to know where a reference is, or the boss wants to know what stored procedure is deleting the sales orders every night, I have to play hide-and-go-seek through all the scripts.

What if someone proposes changing the name of a database field? Having the text of all my object in one file enables me to find all occurances of a field name in any of my scripts. Woo-Ha! Now I know the name of the stored proc or trigger that I have to change.

In the past I would just right click on the server name in Enterprise Manager, and choose 'Generate Scripts'. That gave me exactly what I needed, but in a database this size it took almost an hour. This script runs in about a minute.

 Additionally, it gives me a history. If I run this periodically, I am able to search past versions quickly and see what I changed.

 I'm not going to go into detail about how it works, but I'd be happy to if anyone shows an interest. Just drop me a line.

Version: Unknown or N/A
Section: SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables