Need help finding a column with a specific value.

• fizzleme

  SSCertifiable

  Points: 6906

  More actions

  September 16, 2009 at 5:35 pm

  #215265

  I've got a database with thousands of tables. About a hundred have the column I wish to search. I want those tables that have a specific value in that column. Any script wizards out there?

  When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

  Once you've accepted your flaws, no one can use them against you.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 17, 2009 at 7:54 am

  #1054995

  Something like this will generate the sql you would need to execute:

  DECLARE @sql NVARCHAR(MAX)

  SET @sql = 'select '

  SELECT

  @sql = @sql + C.[name] + ' From ' + T.[name] + ' Where ' + C.[name] + ' = ''[value]'' Union ALL Select '

  FROM

  sys.columns AS C JOIN

  sys.tables AS T ON

  C.[object_id] = T.[object_id]

  WHERE

  C.[name] = 'name'

  SELECT LEFT(@sql, LEN(@sql) - 16)

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question