Today I had cause to take a slightly different direction with a database that had stood for many years providing a fairly critical business function. The change of direction involved dropping a few columns out of a core table with references across an unknown number of procedures and views. What could go wrong?!
Let me start by saying that whilst I spend a lot of time in SQL Server, I’m no DBA and there may well be easier ways of doing this, but in years gone by I would have kicked off by trawling through system tables to find occurrences of the column in question. This worked, but it’s a little clunky and I always had to trawl back through the archives and find the appropriate script to locate those objects.
Enter Red Gate’s SQL Search. Let me start with the important bit –>
Yep, totally free, not a cent to pay. I’ve always said that Red Gate products solve tedious, error prone and downright painful problems in a really cost effective way but the whole zero dollar thing takes it to a whole new level.
What SQL Search does is integrate with SQL Server Management Studio and sit up there on the toolbar waiting to do your bidding:
What this then gives us is a SQL Search tab which occupies the design surface in SSMS. I’m interested – hypothetically – in anywhere “customerid” is used so I just plug that into the search bar. From the first keystroke I instantaneously start getting a filtered list of matches in the results window below:
The other neat features here include the ability to filter by object types (tables, procedures, views, etc.), by database (single DB or just everything) and to choose the server to target. When the results are displayed, clicking on an object gives us highlighted instances of the matches in a lower panel and you can always just “Select object in Object Explorer” to expand the DB tree right down to the item you’re interested in:
So that’s it – and I love the fact that that’s all it does. Focussed, fast and precise. If I want to get really clever about things and invest some more time there’s always SQL Dependency Tracker but I really like the fact that I can just jump in and literally have the info I need in just a few seconds with SQL Search. Go get it – it’s a great little tool and the price is right :)