Have you ever wanted to find all of the stored procedures, functions, maybe even views in SQL Server where you reference a particular column? Or maybe you are planning to change a table and you want find all of the T-SQL code in your database that references that table directly? Or maybe you just want to find all of the objects where you placed a comment like “-- TODO” into you code. Over the years this is something has come up so often for me that I have memorized the SQL query to make it happen:
select
distinct object_schema_name(id), object_name(id)
from sys.syscomments c
where text like '%SomeText%'
Note that there are some pretty important limitations, so if these results are life and death, then you may very well want to use a robust solution instead. You see, the syscomments table breaks up the text of your objects into 8000 character chunks – so if you happen to be so unlucky that your text is broken across that 8000 character boundary and ends up in the two different syscomments records, then you will miss some results. But for a quick and dirty search, this works much of the time.
If you need to be 100% sure you found everything, though, there are some alternatives to consider. You could reverse engineer your database into a Visual Studio Database Edition (VSDB) Database Project – that will bring in the full text of every single object into Visual Studio, where not only can you search, but you can search using options like “whole word” or "match case”. Even better, you can use the Schema Explorer and refactor with built in tools that are smart to parse all of your SQL and really just rename that column you want to rename and not any other columns with a similar name – much more precise than search and replace.
Another alternative would be to use SQL Server Management Studio’s Generate Scripts command to script out all of the objects in your database and then search the output for your text. Depending on how big your object are, though, you might find yourself doing a lot of scrolling around to try and make a list of the objects that reference your search text.
If you are simply looking for dependencies, you could always run some queries on sysdepends. Of course the problem there is that in any real database that wasn’t just created from a script in the perfect order, there may be many missing entries. For example, let’s say that View B depends on Table A. Then you drop and re-create Table A for some odd reason. Unless you drop and re-create View B, View B still works but there is no longer an entry in sysdepends saying that View B depends on View A.
What’s your favorite way to search for some text across objects in a database? Leave a comment and let me know!
Why not use the object explore details view to search for objects? I also found out that you can click edit from the file menu and select find in files and search any directory within any directory.
ReplyDeleteredgate also has a free tool that is cool, and provides a lot of detail searches for objects and is very detailed. See link below. Free is good for poor men. FYI... i love VSDB for searching as well.
http://www.red-gate.com/products/SQL_Search/index.htm?utm_source=sqlsearchui&utm_medium=textad&utm_content=recommend_sql_search&utm_campaign=sqlsearch
Hi redw01f, thank you for reading my blog and commenting! Object Explorer Details view is a very cool feature -- I did not know about this -- thank you for pointing it out. For others that are interested, go into SQL Server Management Studio and then hit F7 or select View -> Object Explorer Details. From there, notice there is a Search box on the toolbar in the new window. This will help you find an object with the search text in its name -- but it will NOT find objects that reference that object, e.g. in stored procedure text.
ReplyDeleteFind in Files from SSMS is also useful if you happen to have the SQL for your objects in files on disk.
Thanks again for sharing your own tips!