This little procedure is very useful during development / support to quickly find all objects that may have something to do with a certain term, column name, part of thereof and so on.
I usually have a keyboard mapping in SQL Studio and when I highlight a column name for example, hit the shortcut, and get a reply with all tables, stored procedures, views that contain that term somewhere in their columns or definitions.
CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024)) AS BEGIN -- tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE charindex(@text, T.TABLE_NAME)>0 -- columns SELECT C.TABLE_NAME, C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C WHERE charindex(@text, C.COLUMN_NAME)>0 -- views SELECT V.TABLE_NAME AS VIEW_NAME FROM information_schema.VIEWS V WHERE charindex(@text, V.VIEW_DEFINITION)>0 -- stored procs SELECT R.ROUTINE_NAME FROM information_schema.routines r WHERE charindex(@text, r.ROUTINE_DEFINITION)>0 END
Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google. Besides the obvious Full-Text search, Phil Factor describes some of the techniques for finding that pesky data that resists the normal SELECT blandishments.
2014-05-01
8,755 reads
Recently Ben Kubicek created a script that would allow him to copy his production db to a test instance and correct the production specific SQL code in stored procedures and views.
2010-02-03
7,425 reads
You can't let a little thing like not knowing a database's structure keep you from your work. Without identifying tables and columns, you can still find specific values and columns.
2009-07-29
2,638 reads
2008-12-02
9,088 reads
This utility will find strings in procedures, functions, views, and even check constraints.
2008-10-31
418 reads
