Few days ago one of my colleague working in another team in my office asked me one question that he have had one table named “PatientDetail” with 58 columns and he wanted to find value from all varchar columns. As a developer the first solution came in my mind was – check value in each column like this FirstName = ‘hello’ OR LastName = ‘hello’ OR…so on. One thing to note here is there are more than 50 varchar columns out of 58 columns. So your query looks something like this –
SELECT * FROM PatientDetail WHERE FirstName = 'hello' OR LastName = 'hello' OR Column3 = 'hello' OR Column4 = 'hello' Or Column5 = 'hello' OR ... Column52 = 'hello'
Don’t you think that above query becomes too much lengthy if i write each column name here. So What is the preferred solution ?
Hmm Do you know that you can use column name in “IN Clause” in sql server!! Really ??
Yes, the above query you can rewrite like this –
SELECT * FROM PatientDetail WHERE 'hello' IN (FirstName, LastName, Column3, Column4, Column5,...)
Well, we just reverse the IN Clause! and this is perfectly valid statement for sql server. Try it yourself. Now the only problem here is we still have to write more than 50 columns in “IN” clause. So to overcome this problem, let’s convert the above query into dynamic sql.
-- *** Prepare comma seperated column list *** DECLARE @ColumnNames VARCHAR(3000) SELECT @ColumnNames = ISNULL(@ColumnNames + ', ' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PatientDetail' AND DATA_TYPE IN ('char', 'nvarchar', 'text', 'varchar') --PRINT @ColumnNames DECLARE @query VARCHAR(MAX)= 'SELECT * FROM PatientDetail WHERE ''hello'' IN (' + @ColumnNames + ')' EXEC SP_EXECUTESQL @query
That’s it. Hope you like this tip. If you know any other solution then post it in the comments. Thank you for reading this post.