- SQL Server Tips for the Non-SQL Developer (Tip #1)
- Find All References To A Column In Stored Procedures (Tip #2)
- SQL Server Merge Statement for Handling SCD2 Changes in a Data Warehouse (Tip #3)
- Create POCO Object from SQL (Tip #4)
I personally don’t know how many times I’ve been asked which stored procedures reference a specific column. Now I don’t mean this happens weekly but it happens enough that I’m sure I’m not the only one that gets asked this question.
An easy starting place for this question is to look at the “View Dependencies” in SSMS which I showed in the first tip. This is very helpful in regards to showing you every stored procedure that references a table, but again does not tell you what columns are being used.
The first time I went to get this list, I used the following query in Figure 1 taking advantage of another of the “Information_Schema” views which is “Routines”. This view contains a list of all the stored procedures and functions that are in a specific database. I looked at the column definitions for this view from Microsoft and saw that I could use the column “Routine_Definition” since it is the actual t-sql of the stored procedure or function. I assumed that the SQL in figure 1 would work for finding all stored procedures and functions that contain my search string as long as they were not encrypted.
From the SQL in query 1 you can see I am looking for the string “Tasophdrrecalc” which is actually another stored procedure; I was hoping to find all the references to it or at least the one reference that I knew for sure existed. However, as you can see, when I ran the SQL my query didn’t return any results making me think there weren’t any stored procedures that referenced my search string, even though I knew there was at least one. So how come the one reference I knew existed didn’t get returned?
In order to find out why that one stored procedure that I knew referenced my string of text didn’t show up, I ran the SQL in Figure 2. This statement will give you the text that was used to create the stored procedure, function, or view as long as it isn’t encrypted. After running this I was able to determine why I wasn’t finding the stored procedure – the view was actually truncating the text used to create the procedure or function to 4000 characters, and the stored procedure I knew referenced this string contained more than 4000. Figure 3 shows most of the SQL used to create the view and I have highlighted the area where the truncation takes place.
After running the “sp_helptext” I was able to figure out how to get the results that I wanted. I wanted a way to query the database functions and stored procedure and find every reference of a string of text that existed, no matter what the character length of the stored procedure was. I chose to create my own stored procedure that would look at the entire definition of the stored procedure or function. I also removed a lot of the columns from the original view because for this scenario, I really didn’t care about most of the information that was returned. All I wanted to know was did the text exist in another stored procedure or function, and if so, what the name of that procedure or function was. I ended up creating the stored procedure in Figure 4. The stored procedure accepts one parameter which is the search string. I have also added a where clause to the procedure that uses column that was truncated in the view to find my search string.
Now when I run this stored procedure I actually get the results that I expected to see as shown in Figure 5.
I have attached the stored procedure that I created for this article. Download the source code here.