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
- Open up Microsoft SQL Server Management Studio.
- Open a new query window.
- Make sure it is set to the specific database you are trying to search. Please not this search query does not work across databases.
- Paste the following code into a new query window.
123456789101112131415DECLARE @strSearch varchar(max);--**** Replace 'Col' with what you are searching forSET @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 ModifiedFROM sys.sql_modules INNER JOINsys.objects ON sys.sql_modules.object_id = sys.objects.object_idWHERE (sys.sql_modules.definition LIKE '%' + @strSearch + '%');
- Replace the “@strSearch” variable with whatever you are trying to find in the current database.
- 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.
- 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.