How can I SELECT COLUMNS LIKE '%SOMETHING%'?

1

Hello!

How can I SELECT COLUMNS LIKE '%SOMETHING%' from a TABLE?
How can I SELECT COLUMNS from TABLES LIKE '%SOMETHING%'?

Thanks!

2

Is this what you are looking for..

Select Col1,Col2,Col3 From Table1 Where Col1 LIKE '%SOMETHING%'

3

Unfortunately not.

I am looking for something like this:

SELECT LIKE '%SOMETHING%'
FROM TABLE1

and

SELECT *
FROM LIKE '%SOMETHING%'

I think the second does not make much sense.

I got some replies in another post but I wanted to ask if there is a quick trick, instead of resolving to sys files.

Thanks!

4

Both of your queries are incorrect, I am not understanding what exactly you are looking for.
Can you point to the other topic you are talking about but if ppl have replied in there then I am guessing that might be the way to go..but still can you post the link here for that topic

Maybe you looking to select/list all columns from a table, again just a guess

5

If the objective is to return columns from a table where the NAME of the columns match a particular pattern, then you will have to use dynamic SQL to achieve that goal.

The same is true if your objective is to select data from a table where the NAME of the table matches a particular pattern.

See here for a tutorial on dynamic SQL

Using dynamic SQL brings along with it some security concerns. Best practice recommendations are to avoid dynamic SQL where possible. To get an understanding of how to use it, and the security risks associated with it these articles may be useful.

  1. Gail Shaw
  2. Erland Sommarskog

More often than not, it may be possible to achieve the goals without resorting to dynamic SQL. Usually, the need to have data retrieved from unknown columns or unknown tables arises out of less than ideal database design.

If you post the problem you are trying to solve in more basic terms, people on this forum may be able to offer safer/better/simpler alternatives.

3 Likes
6

Neither makes sense. So instead of you posting the solution, what is it you are trying to solve?

7

There are columns related to Product. I want to capture all these columns and return their top 10 values.

8

are these columns in 1 table or in many tables?

9

Looks like you have asked a similar question many times before but might not have bothered to look at the answers

1 Like
10

One Idea is .. to script it ..

before i start .. Information_Schema.Columns .. is not reliable .. There is some post by Scott Pletcher which uses sysobjects .. syscolumns something .. just lazy to find it

Information_Schema.Columns ..

select
' select top 10 ',column_name ,'from ',table_name
from
information_schema.columns
where
table_name = 'Product' and column_name like '%xxx%'