SQL Server 2012 Search – How to in MSSQL

Following on from yesterdays post on how to debug a stored procedure i decided to do a post on how to conduct a sql server 2012 search and by this i mean finding anywhere that a column is referenced in a stored procedure or function or view. This really helps if you have to make a change to a column name or resize a column and need to find every reference to that column to tidy it up further down the line.

SQL Server 2012 Search – How to

1. Open up Microsoft SQL Server Management Studio.
2. Open a new query window.
3. Make sure it is set to the specific database you are trying to search. Please not this search query does not work across databases.
4. Paste the following code into a new query window.

   DECLARE @strSearch varchar(max); 
   
   --**** Replace 'Col' with what you are searching for
   SET @strSearch = 'Col';
   
   SELECT sys.objects.name AS FoundInObject, 
    SUBSTRING(sys.sql_modules.definition, CHARINDEX(@strSearch, sys.sql_modules.definition) - 30, 150) AS [InCode], 
    CASE WHEN type = 'p' THEN 'Stored Procedure' 
    WHEN type = 'IF' THEN 'Function' 
    WHEN type = 'V' THEN 'View' ELSE Type END AS [Type], 
    sys.objects.create_date AS Created, 
    sys.objects.modify_date AS Modified
   FROM sys.sql_modules INNER JOIN
    sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
   WHERE (sys.sql_modules.definition LIKE '%' + @strSearch + '%'); 

5. Replace the “@strSearch” variable with whatever you are trying to find in the current database.
6. Run the query and you should now see a list of results if your criteria was found in the database.
   • The “FoundInObject” is the object that the search term was found in.
   • The “InCode” defines whereabouts in the object it was found.
   • The “Type” defines what type of sql object it was found in, for example a view, stored procedure or function.Please note as this is a “like” query and it will not match exact but also find anything that contains your search term. For example if i had a column named “Data” and a column name “Database” and i searched for “Data” it would return both matches.
7. After this point you can then analyse and make any necessary changes.

I believe this query should apply to any version of MSSQL from 2000 onwards. Please note i have amended it over time and i do not have a 2000 environment to try it on at present so if anyone is still using 2000 and has any problems running it then please let me know in the comments.

Comments

comments