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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 13, 2012 at 12:36 pm

  #1558613

  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

  November 13, 2012 at 12:39 pm

  #1558616

  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

  November 13, 2012 at 12:40 pm

  #1558618

  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: 323520

  More actions

  November 13, 2012 at 12:42 pm

  #1558620

  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

  November 13, 2012 at 1:09 pm

  #1558626

  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

  November 13, 2012 at 1:15 pm

  #1558629

  That one worked perfectly!!!!