Solved: find column in all stored procedures sql server

When working with large databases in SQL Server, you may inevitably come across a situation where you need to find a particular column across all stored procedures. This could come up if you are refactoring your code, hunting down bugs, or simply curious about the usage of a column within your procedures. Whatever the reason, locating a column in SQL Server can seem like a daunting task, but fret not—with the right SQL script, this can be made quick and easy.

We can achieve this using the `INFORMATION_SCHEMA.ROUTINES` table or `sys.sql_modules`, where the text of each procedure is stored in SQL server.

[b] Let’s take a practical view at how to find column in all stored procedures in SQL Server. [/b]

Solution Overview

Our basic strategy will be to query the `INFORMATION_SCHEMA.ROUTINES` table (or `sys.sql_modules`) to retrieve the definition of every stored procedure. We can then use `LIKE` statement to search for our column within these definitions. It’s as simple as that!

If you’re looking to accomplish this in your own SQL Server environment, you could utilize this generic query:


Step-by-Step Code Explanation

1. Selecting the Necessary Information


2. Filtering the Definition

Then, we use a `WHERE` clause to filter the `ROUTINE_DEFINITION`. We are interested in only those rows where the `ROUTINE_DEFINITION` contains our column name.

3. Limiting to Stored Procedures

Finally, we add another condition in the `WHERE` clause to limit our result set to only those routines that are stored procedures.

Alternative Approach using sys.sql_modules

The above query will work perfectly fine for finding column name in the stored procedure. However, there is an alternate way to get the same information. We can use `sys.sql_modules` system view to get the text of the SQL Server modules. It includes definitions of stored procedures, functions, triggers, etc.

SELECT Object_name(sm.object_id) as name, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition like '%ColumnName%'

Utilizing SQL Wildcards

SQL wildcards can be very useful here. If you are unsure whether spaces or other characters might precede or follow your column name, you can use the `%` wildcard like so `’% ColumnName %’` to encompass any characters that may be present.

With these straightforward steps, you can now easily find a column throughout all of your stored procedures in SQL server. The utility of such feature is not limited to finding columns, but extends to any text you wish to spot across your stored procedures.

Related posts:

Leave a Comment