Searching Stored SQL – Part 1

Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES.

Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red Gate. But this tool does not list the actual line number in the code that a pattern was found. Also, advanced search logic like “find all stored procedures that have a variable named T1 and initials of JFM, the SQL developer on the project” can not be performed.

Given this dilemma, I decided to write a tool, a user defined stored procedure, to do just want I wanted. There is a nice system stored procedure called sp_helptext that returns the stored SQL code (text) for any object. Today, I am going to write a tool for use with stored procedures.

The next step in the process is to write down psuedo code for the algorithm I wanted.

1 – Ask the user for a database name.
2 – Create a global temp table in tempdb to store results.
3 – Dump details of the objects into a local temp table.
4 – Add information like database name, schema name, object name and line numbers.

The header section of the CREATE PROCEDURE defines any parameters that are passed as input. I am going to ask the user for a valid database name and a optional verbose indicator.

Here are the TRANSACTIONAL SQL statements I am going to use for Step 1.

IF – statement allows for conditional execution of code.
BEGIN & END – statements mark the start and stop of a code block.
PRINT – statement returns messages to the output window in SSMS.
RETURN – statement exits the stored procedure immediately.

The first task in step 1 of the algorithm is to make sure that the name of the database is valid. I first test the variable to make sure that it is not equal to the default value of ‘*’. If it is the default, the user did not pass a value.

The second task in step 1 of the algorithm is to make sure the name is valid. This is done by check the system view sys.databases for the supplied name.

The Step 2 of the algorithm is where a global temp table is defined in tempdb. I want the tool to be able to have more than one user at a time. Therefore, we need to dynamically create a table named after the user and the object (s)he is looking for. Please note, the table will exist in tempdb until the server is restarted.

Here are the TRANSACTIONAL SQL statements I am going to use for Step 2.

SUSER_ID() – function returns the current user id.
SUSER_NAME() – function converts the id into a textual windows domain and login user.
REPLACE() – function switches all occurences of the search string with a new string.
ISNULL() – function uses value one if it is not null, else it uses value two.
DECLARE – statement is used to declare local variables.
SET – statement is used to perform assignment with variables.
EXISTS() – function determines if a result set is not empty.
EXECUTE() – statement runs dynamic SQL.

Step 2 can be broken down into two sub-tasks. If the table exists, (TRUNCATE TABLE) clear the data. If the table does not exist, create the table from scratch (CREATE TABLE). Both tasks use dynamic SQL.

Step 3 of the algorithm is where a local temporary table is create to store the fully qualified name of user defined objects for a given database.

Step 4 of the algorithm is where a cursor is DECLARED, OPENED, FETCHED NEXT, CLOSED and DEALLOCATED.

Cursors are a great object for traversing over a small result set for administrative purposes.
A infinite WHILE loop is used to process the records until the @@FETCH_STATUS variable is non-zero. A non-zero condition indicates that there is no more data. The BREAK statement is used to exit the loop at that point.

Step 4 can be broken down into two sub-tasks which process each record in the temporary table created in step 3. For each record, add the output of the sp_helptext system stored procedure to the user specific, global temp table in tempdb. Update the output for the current record to reflect database name, schema name, object name and line numbers.

The last part of the stored procedure is to tell the user where to find the output table in tempdb. This is done by using the PRINT statement to echo the name of the results table.

Enclosed is the full stored procedure for your usage.

A sample call to the tool to list and save all stored procedure code in the AdventureWorks database to a user defined table.

We can filter by object name if it is known. I am looking for the update employee hire info.

We can filter by actual code if we know a key value. I am looking for the hire date.

The image below show the results of this search.

Again, the user defined table created by this procedure stays in the system until tempdb is recreated upon server startup.

The system views named sys.objects and sys.schemas are key concepts in this solution. In upcoming articles, I will explain how this query needs to be adjusted for the four different objects that have stored SQL.

Related posts

4 Thoughts to “Searching Stored SQL – Part 1”

  1. Saved as a favorite, I love your blog! :)

  2. Well, this really is my very first visit to your site! We are a gaggle of volunteers and starting a new effort in a neighborhood in the very same niche. Your website provided us all valuable information to work in. You have done any marvellous work!

  3. Rattling nice pattern and excellent content material, absolutely nothing else we want :D.

  4. Terry Massman

    I am unable to thank you more than enough for the posts on your website. I know you place a lot of time and energy into these and hope you know how considerably I enjoy it.

Leave a Reply to Terry Massman Cancel reply