Select row where any column contains a search string

In this post, I will explain SQL Query to Select row where any column contains a search string with a below example.

Below example, we are trying to select rows from an Employee_Master table where any column of a table contains the same text as TextToSearch.

1. QUERY TO CREATE TABLE

CREATE TABLE Employee_Master(

FirstName varchar(50),

MiddleName varchar(50),

LastName varchar(50)

)

GO

2. QUERY TO  INSERT DATA

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'XYZ', N'ABC', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'ABC', N'XYZ', N'DEF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'EEE', N'FFF')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'KKKK', N'XYZ')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'WSD', N'XYZ', N'WER')

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'AAA', N'', NULL)

GO

INSERT [dbo].[Employee_Master] ([FirstName], [MiddleName], [LastName]) VALUES (N'', N'AAA', N'')

GO

3. QUERY TO SEARCH STRING IN ANY COLUMN

DECLARE @SqlQuery NVARCHAR(4000)

DECLARE @ColumnList NVARCHAR(4000)

DECLARE @TextToSearch NVARCHAR(200)

DECLARE @TableName NVARCHAR(200)

SET @TableName = 'Employee_Master'

SET @TextToSearch = XYZ

SET @ColumnList =

(

SELECT LEFT(Column_Name, LEN(Column_Name) - 1)

FROM ( SELECT Column_Name + ', '

FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tableName

FOR XML PATH ('')

) CN (Column_Name )

)

SET @SqlQuery = 'SELECT * FROM EMPLOYEE_MASTER WHERE ''' + @TextToSearch + '''  IN (' + @ColumnList + ')';  

EXEC SP_EXECUTESQL @SqlQuery

GO