Searching SQL Scripts using SQLDMO and Regular Expressions

The 3--tier system architecture (Database, Business Logic, User Interface) is a popular development pattern nowadays. If your team is working in a 3-tier mode, the chances are that you write the Business Logic and User Interface code in an IDE like Visual Studio and write SQL database scripts in SQL Query Analyzer or SQL Server Enterprise Manager. While it's pretty easy to search your BL and UI code in a well-designed IDE, it's painstakingly slow to search your SQL scripts. In this article I will show you a solution using the SQLDMO COM object and the .NET framework. We will generate all the scripts for our SQL Server objects (incl. Tables, Triggers, Views, Defaults, Rules, UserDefinedDatatypes, UserDefinedFunctions, StoredProcedures) with SQLDMO and search the generated scripts with Regular Expressions. We will then highlight the matches and reformat the code to make it HTML friendly and render the result to a web page.