How can I look up columns

• marty.seed

  SSCrazy

  Points: 2661

  More actions

  August 13, 2008 at 1:17 pm

  #190944

  Hello, in the old versions of SQL Server you could do a wildcard search on a column name, does 2005 have this anywhere? I need to do a search for a variety of fields and don't have time to go through every table?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 13, 2008 at 1:34 pm

  #857687

  Use BOL and read up on sys.columns or execute some T-SQL for example

  To return all column names:

  SELECT * FROM sys.columns

  To search for a particular column

  SELECT object_id,name FROM sys.columns WHERE name LIKE '%insert your column name here%'

  object_id is the ID of the object(table/view) to which this column belongs

  or do something like this

  CREATE procedure [dbo].[FindColumnByName]

  @findcolumn varchar(50)

  as

  begin

  set nocount on

  select sysobjects.name as TableFound,syscolumns.name as ColumnFound

  from sysobjects

  inner join syscolumns on sysobjects.id=syscolumns.id

  where sysobjects.xtype=''U''

  and (syscolumns.name like ''%'' + @findcolumn +''%''

  or sysobjects.name like ''%'' + @findcolumn +''%'' )

  order by TableFound,ColumnFound

  end

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]