Determine all tables where fieldname exists

• SandyC3339

  SSC Enthusiast

  Points: 126

  More actions

  November 3, 2003 at 7:41 am

  #83180

  Is there a table in SQLSERVER that I can query that will tell me all tables that use a particular field?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 3, 2003 at 7:44 am

  #480638

  you mean something like

  
  
  SELECT 
  
  table_schema, table_name 
  
  FROM 
  
  information_schema.columns
  
  WHERE
  
  column_name = 'MsgDateReceived'
  
  
  
  or 
  
  
  
  SELECT 
  
  s2.name 
  
  FROM 
  
  dbo.syscolumns s1 
  
  JOIN 
  
  dbo.sysobjects s2 
  
  ON 
  
  s1.id = s2.id 
  
  WHERE 
  
  s1.NAME='MsgDateReceived' 
  
  
  
  or
  
  
  
  SELECT 
  
  table_schema, table_name 
  
  FROM 
  
  information_schema.columns 
  
  WHERE 
  
  column_name  like 'MsgDateReceived' 
  
  AND 
  
  objectproperty(object_id(table_name), 'IsUserTable') = 1 
  
  ORDER BY 
  
  column_name 
  
  

  Frank

  http://www.insidesql.de

  http://www.familienzirkus.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]