tracking a column in sql server database

• jprabha.d

  Ten Centuries

  Points: 1086

  More actions

  November 27, 2009 at 11:35 pm

  #214409

  hi...

  Could anyone help me out from this....

  I have a particular column say it as "xxxxxx" ,since there are nearly 2000 tables in a database.

  scanning all the tables and checking out for this column could take a lot of time to do.

  Is there any method or query that can be used to easily find out in which table this column exists,

  thanks in advanced

  jaya

• SQL Editor

  SSCommitted

  Points: 1980

  More actions

  November 28, 2009 at 12:20 am

  #1085075

  select * from information_schema.columns where column_name='your search column name'

  Place Your search column name in condition and find your desire result.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  November 28, 2009 at 1:22 am

  #1085082

  jprabha.d (11/27/2009)

  ---

  Is there any method or query that can be used to easily find out in which table this column exists,

  Hi,

  Also use this,

  For search in the current database

  select object_name(id)TableName from syscolumns

  where name = ‘your string’

  For search in all database

  exec sp_MSforeachdb "select '?' As DatabaseName,object_name(id)TableName

  from ?.dbo.syscolumns where name = (‘your string’)"

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 28, 2009 at 1:43 am

  #1085083

  arun.sas (11/28/2009)

  ---

  Hi,

  Also use this,

  For search in the current database

  select object_name(id)TableName from syscolumns

  where name = ‘your string’

  syscolumns is a deprecated system view and will be removed in a future version. Use sys.columns instead.

  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