Technical Article

Searching through SPs, Tables, Views

,

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

Rate

4.25 (8)

Share

Share

Rate

4.25 (8)