Following on from the Search every table and field in a SQL Server Database article I posted last week, I thought it would be useful also to be able to search every column for some text and return where it was found.

Here is the script that I have produced which does the job and is quick in its response.

CREATE PROC [SearchAllColumns]
(
 @SearchStr nvarchar(100)
)
AS
 
BEGIN
 
 SELECT COLUMN_NAME AS 'ColumnName',
 TABLE_NAME AS 'TableName'
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME LIKE '%' + @SearchStr + '%'
 ORDER BY TableName,
 ColumnName;

END