Iterate through field names in a table

Is it possible to (1) query all the fields in particular table (2) and iterate through the fields via a loop/cursor?
patriotpacerAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
I am not sure you need to use a UNION as you can just do one query with OR conditions. UNION may help if there are indexes on these columns that will operate better as a single conditional. The solution is the same, though. You use the INFORMATION_SCHEMA.COLUMNS view to get the list of columns that meet data types you want to query, then build your dynamic SQL statement. For example, a LIKE query likely is for VARCHAR columns.

DECLARE @tablename AS SYSNAME = 'IMC';
DECLARE @sqlCondition AS VARCHAR(MAX);

SELECT @sqlCondition = COALESCE(@sqlCondition + CHAR(13) + '   OR ', '') + '[' + COLUMN_NAME + '] LIKE ''SEARCH TERM%'''
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @tablename
AND c.DATA_TYPE IN ('VARCHAR', 'NVARCHAR')
;

PRINT('SELECT * FROM ' + @tablename + CHAR(13) + 'WHERE ' + @sqlCondition + CHAR(13) + ';');

Open in new window


NOTE: the CHAR(13) is not a requirement for the solution. I added so the printed SQL looks formatted. Just my OCD. *smile*
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes.   Give us a 'for example' of what you're trying to pull off here, and we should be able to provide you with T-SQL.
0
 
patriotpacerAuthor Commented:
Sure.

Maybe you can suggest a better way.

What I'm trying to do (or what i was TOLD to do) is do "like" searches on EACH field in a table.

So trying to pull all the fields from one table, iterate through each field, and create dynamic SQL that does like searches on each field as it iterates through.  Hoping to return a union.

Hopefully that makes sense.
0
 
patriotpacerAuthor Commented:
I love it!

Glad I mentioned the union.  Unnecessary hit to performance.
0
 
patriotpacerAuthor Commented:
thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.