Searching a particular table in DB

• Jim1234

  SSC Eights!

  Points: 889

  More actions

  September 15, 2013 at 5:35 am

  #297749

  Hi All,

  I have list of columns (say col1,col2,col3). I need to find the table in the database which contains these 3 columns. Please suggest.

  Regards

  Jim

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 15, 2013 at 5:45 am

  #1650274

  You can query the sys.columns metadata view to see what tables have particular columns in them.

  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

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 16, 2013 at 6:09 am

  #1650391

  select * from information_schema.columns

  where column_name like '%col1%'

  OR column_name like '%col2%' OR column_name like '%col3%'

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  September 16, 2013 at 10:03 am

  #1650515

  SELECT

  OBJECT_NAME(object_id) AS table_name

  FROM sys.columns

  WHERE

  name IN ( 'col1', 'col2', 'col3' ) AND

  OBJECTPROPERTYEX (object_id, 'BaseType') IN ( 'U', 'V' )

  GROUP BY

  object_id

  HAVING

  COUNT(*) = 3

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.