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)