As a BI Consultant, I am frequently going into new databases and writing queries against unfamiliar data sources. I have created a stored procedure that goes against the system views to help quickly find a key word in a Table, View, Columns or Stored Procedure.

Once the procedure has been created you can execute it will any string you wish to search. You can also limit your results to a column, table, or stored procedure (Views are also searched in the table or column searches). The one downside to this is the ObjectSearch procedure needs to exist in each database you want to use it in.

 
EXEC dbo.uspObjectSearch 'test'
EXEC dbo.uspObjectSearch 'test', 'Table'

Once you have created the procedure you can create a keyboard shortcut to call the stored procedure. This will let you highlight any string in Management Studio and quickly find any references to it. To set up a keyboard shortcut see my previous post SQL Custom Keyboard Shortcuts.

I have set my search to Ctrl+4: EXEC dbo.uspObjectSearch

CREATE PROCEDURE  dbo.ObjectSearch
	@SearchString VARCHAR(200),
	@SearchObject VARCHAR(50) = 'All'
AS 

/*
@SearchObject
All, Proc, Table, Column
*/

--DECLARE @SearchString VARCHAR(200)
--DECLARE @SearchObject VARCHAR(50)
--
--SELECT @SearchString = 'customer',
--	@SearchObject = 'All'

SELECT @SearchString = '%' + @SearchString + '%'

IF @SearchObject = 'All'
	BEGIN
		--Column
		SELECT  'Column' AS SearchObject,
				C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, T.TABLE_TYPE,
				C.COLUMN_NAME, C.ORDINAL_POSITION, C.COLUMN_DEFAULT, C.IS_NULLABLE,
				C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.CHARACTER_OCTET_LENGTH, C.NUMERIC_PRECISION,
				C.NUMERIC_PRECISION_RADIX, C.NUMERIC_SCALE, C.DATETIME_PRECISION, C.CHARACTER_SET_CATALOG,
				C.CHARACTER_SET_SCHEMA, C.CHARACTER_SET_NAME, C.COLLATION_CATALOG, C.COLLATION_SCHEMA,
				C.COLLATION_NAME, C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA,C.DOMAIN_NAME
		FROM    INFORMATION_SCHEMA.COLUMNS AS C
		INNER JOIN INFORMATION_SCHEMA.TABLES AS T
			ON T.TABLE_NAME = C.TABLE_NAME
			AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
			AND T.TABLE_CATALOG = C.TABLE_CATALOG
		WHERE   C.COLUMN_NAME LIKE @SearchString
		ORDER BY T.TABLE_NAME

		--Table
		SELECT 'Table' AS SearchObject, *
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME Like @SearchString
		ORDER BY TABLE_NAME

		----Stored Proc
		SELECT  'Stored Proc' AS SearchObject,
				ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
				DATA_TYPE,ROUTINE_DEFINITION, CREATED, LAST_ALTERED
		FROM    INFORMATION_SCHEMA.ROUTINES
		WHERE   ROUTINE_DEFINITION LIKE @SearchString
		ORDER BY  ROUTINE_TYPE DESC, ROUTINE_NAME
	END --ALL

ELSE IF @SearchObject = 'Proc'
	BEGIN
		SELECT  'Stored Proc' AS SearchObject,
				ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
				DATA_TYPE,ROUTINE_DEFINITION, CREATED, LAST_ALTERED
		FROM    INFORMATION_SCHEMA.ROUTINES
		WHERE   ROUTINE_DEFINITION LIKE @SearchString
		ORDER BY  ROUTINE_TYPE DESC, ROUTINE_NAME
	END --Proc

ELSE IF  @SearchObject = 'Table'
	BEGIN
		SELECT 'Table' AS SearchObject, *
		FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME Like @SearchString
		ORDER BY TABLE_NAME
	END --Table

ELSE IF  @SearchObject = 'Column'
	BEGIN
		SELECT  'Column' AS SearchObject,
				C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, T.TABLE_TYPE,
				C.COLUMN_NAME, C.ORDINAL_POSITION, C.COLUMN_DEFAULT, C.IS_NULLABLE,
				C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.CHARACTER_OCTET_LENGTH, C.NUMERIC_PRECISION,
				C.NUMERIC_PRECISION_RADIX, C.NUMERIC_SCALE, C.DATETIME_PRECISION, C.CHARACTER_SET_CATALOG,
				C.CHARACTER_SET_SCHEMA, C.CHARACTER_SET_NAME, C.COLLATION_CATALOG, C.COLLATION_SCHEMA,
				C.COLLATION_NAME, C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA,C.DOMAIN_NAME
		FROM    INFORMATION_SCHEMA.COLUMNS AS C
		INNER JOIN INFORMATION_SCHEMA.TABLES AS T ON T.TABLE_NAME = C.TABLE_NAME
		WHERE   C.COLUMN_NAME LIKE @SearchString
		ORDER BY T.TABLE_NAME
	END --Table 

ELSE
	SELECT '@SearchObject be one of the following values (All, Proc, Table, Column)'