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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
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.
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*

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patriotpacerAuthor Commented:
I love it!

Glad I mentioned the union.  Unnecessary hit to performance.
patriotpacerAuthor Commented:
thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.