Search column name in database?

Question

Hi all:

what i am trying to do is search one column name in whole database.  i used to achieve this by using Query Analyzer .

Can anyone tell me does this function available in SQL 2005?

Thanks

Nick

Answer 1

Hi Nick,

Yes, this information is still available via INFORMATION_SCHEMA.columns.

Cheers,

Rob

Answer 2

Thanks. mate.

Can i ask how to search a text in stored procedure, view. and UDF?

 

Cheers

Nick

Answer 3

Hi Nick,

Sure: you can get this info from syscomments.  So, to find "HelloWorld", you would:

select object_name(id)

from syscomments

where text like '%HelloWorld%'

Cheers,

Rob

Answer 4

In addition to rob you can use the INFORMATION_SCHEM.Routines (column Routine_definition) to search within the procedure text.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 5

Robert Varga wrote:

Hi Nick, Yes, this information is still available via INFORMATION_SCHEMA.columns. Cheers, Rob

You can use it with this syntax
SELECT *
  FROM INFORMATION_SCHEMA.columns
  WHERE Column_Name = 'TheColumnNameYouAreLookingFor'

Answer 6

Hi,

In the following thread there are a few methods of finding columns with a specified name within a database.

http://www.kodyaz.com/forums/thread/56.aspx