Ok folks…this is really old school. I still use it because it is quick, easy and flexible. The following query may be used to find in any database object containing text you suppl. It checks tables, views, functions and procedures.
When I say it checks tables, I mean it checks the definition of a table, not the data contents stored within a table. This query is written for MS SQL Server and works with SQLServer 2005 and later. Take out the SYS schema designation, and it will work with SQL Server versions going back as far as 4.21a.
The following query will find objects having the text “BEN” contained somewhere in the the definition. Because it searches using the LIKE operator, you can put any number of wildcards supported by the LIKE operator.
DECLARE @Search NVARCHAR(1000)
SET @Search = N'%BEN%'
SELECT DISTINCT
so.name as ObjctName
,so.type
,sc.text
FROM SYS.SYSCOMMENTS sc
JOIN SYS.SYSOBJECTS so ON sc.Id = so.Id
WHERE sc.text LIKE @Search
ORDER BY so.type
,so.name
This query has a lot of power because you can look for column names, object names, and much more. Suppose you want to find any object that modifies a specific table. You can define a number of compairson strings to find Insert, Update, and Delete statements. Depending on your configuration, it may even return triggers, which are a handy place to hide code that modifies your tables.
SQL Server has gotten much better at tracking dependencies. Also, many of the tools, including SQLServer Management Studio, are better than they used to be for finding database dependencies, or searching for schema text. So, you may not find you need a query such as this. However, this query always works unless the SQL objects are encrypted.
Feel free to share your tips in our comments below on this or any other SQL problem you face.
Cheers,
Ben
We do have a response to the SQL Puzzler from last week.
Anna shares her solution, using a loop based on row count, replacing the cursor.
I am still using SQL 2005. I always try to avoid using a CURSOR.
Probably my solution is not of the best but was able to resolve the
puzzler.
begin tran
while exists(select s.SomeText,cnt=count(s.SomeText)
from #Abbreviations as a
left outer join #Strings as s on s.SomeText like '%'+
a.SearchValue + '%'
where s.SomeText is not null ---and cnt<>0
group by s.SomeText
having count(s.SomeText)<>(select count(*) from
#Abbreviations)
)
begin
update s
set
s.SomeText=replace(s.SomeText,isnull(a.SearchValue,''),isnull(a.Abbrevia
tion,''))
from #Strings as s
left outer join #Abbreviations as a on s.SomeText like '%'+
a.SearchValue + '%'
end
select * from #Strings
ROLLBACK
Result:
Mst and Slv Regr
Hdr and Ftr Cyl
Rt Side Lever
Lft Side Lever