Regarding column value

Question

Regarding column value

Bilichi

Right there with Babe

Points: 741
More actions
March 27, 2008 at 3:38 am

#188546

Hi all,
i need a query to get what are the tables that have the value 'cat'.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Are you trying to search for 'cat' in a particular column or every column of every table? What exactly is the purpose for this search?

My blog: http://jahaines.blogspot.com

Anam Verma SSCertifiable Points: 5365 More actions · Answer 2

Can you please clarify that you need 'cat' as value in column or substring in column name?

Bilichi Right there with Babe Points: 741 More actions · Answer 3

Hai all,

Suppose i am having 10 tables in a database.in that 10 tables,5 tables are having the column value as 'cat'.i need 5 tables name alone.more over it must be a single query.please don't go for cursor & while loop.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

Hi,

I quess you should use the UNION for this.

SELECT Column1, Column2 FROM Table1 WHERE Column1='cat' OR Column2='cat'

UNION ALL

SELECT Column1, Column2 FROM Table2 WHERE Column1='cat' OR Column2='cat'

UNION ALL

SELECT Column1, Column2 FROM Table3 WHERE Column1='cat' OR Column2='cat'

UNION ALL

......

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

vmsbalaji (3/28/2008)
Hai all,
Suppose i am having 10 tables in a database.in that 10 tables,5 tables are having the column value as 'cat'.i need 5 tables name alone.more over it must be a single query.please don't go for cursor & while loop.

This will do it...

SELECT Table_Catalog AS DBName,

Table_Schema AS Owner,

Table_Name AS TableName

FROM Information_Schema.Columns

WHERE Column_Name = 'Cat'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

vmsbalaji (3/28/2008)
Hai all,
Suppose i am having 10 tables in a database.in that 10 tables,5 tables are having the column value as 'cat'.i need 5 tables name alone.more over it must be a single query.please don't go for cursor & while loop.

Value of the data in the column, or column name cat? Can you give an example of how you would do it with 1 table? You are being very unclear. Is the column name the same in all cases?

Or are we talking about looking for the value of 'cat' in every row of every column of every table in every database?

Please try to re-word again.

Thank you