Search All Tables

Question

Search All Tables

jordon.shaw

Hall of Fame

Points: 3571
More actions
November 13, 2012 at 11:58 am

#393595

Okay, there has to be a better way to to what I want to accomplish, I just can't figure it out. I want to search all my tables in the database and then tell me which one has a column name = Compkey and that Compkey is = 270188. I wrote the following script:
select 'SELECT * FROM' + ' ' + TABLE_SCHEMA+'.'+ TABLE_NAME + ' WHERE ' + COLUMN_NAME + '=''270188''' --ENTER COMPKEY HERE
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'COMPKEY'
order by TABLE_SCHEMA
This works, the only problem is I then get 411 queries that I need to run to find the tables that has compkey = 270188. Any suggestions here?
Thanks!!!
Jordon

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nope, that's pretty much how it's done.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 2

GilaMonster (11/13/2012)
Nope, that's pretty much how it's done.

Well at least I know that I haven't completely lost my mind. Do you know of a way for all those query results to go to a textfile, so that I can run them all at once and have a log file at the end?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In management studio, select results to file and specify the file name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 4

create a temp table,and change the query to insert something into the table instead of a simple select...maybe the table name and the column name or something...or a PK...

you can use select * because every table could have a different schema.

CREATE TABLE #Global(ColumnList varchar(30))

select 'INSERT INTO #Global SELECT ColumnList FROM' + ' ' + TABLE_SCHEMA+'.'+ TABLE_NAME + ' WHERE ' + COLUMN_NAME + '=''270188''' --ENTER COMPKEY HERE

from INFORMATION_SCHEMA.COLUMNS

where COLUMN_NAME = 'COMPKEY'

order by TABLE_SCHEMA

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spongemagnet SSCrazy Points: 2812 More actions · Answer 5

select 'select ''' + a.TABLE_NAME + ''' as TABLE_NAME from [' + a.TABLE_SCHEMA + '].[' + a.TABLE_NAME + '] where COMPKEY = 270188 union'

from INFORMATION_SCHEMA.TABLES a

join INFORMATION_SCHEMA.COLUMNS b

on a.TABLE_NAME = b.TABLE_NAME

and a.TABLE_SCHEMA = b.TABLE_SCHEMA

and b.COLUMN_NAME = 'COMPKEY'

Try something like this, will be slightly less painful. Manually remove the trailing "union", copy/paste/run and you should get a list of table names where that column with that value appear.

-Ken

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 6

jordon.shaw

Hall of Fame

Points: 3571

November 13, 2012 at 1:15 pm

#1558629

That one worked perfectly!!!!