How to find which table a field is in SQL2000

[chrishall314]

I have a copy of a clients database in SQL Server 2000, and there is about 700+ tables. I know there are 26 fields somewhere in the database, but I don't know where they are. So I would like to know if there is a good way to search through all tables to find a particular field. By good way, I mean not opening each table and looking for the field name. I am in process of asking the client, but getting through the political red tape is taking forever. Also please let me know if this is even possible, so I can stop looking if it is not.

Thanks in advance
Chris

 

[tlbroadbent]

Very simple. Use the Information_schema views provided in every SQL database. SQL BOL contains details.

Select *
From Information_schema.Columns
Where column_name = 'colname' Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[Zathras]

There's probably a system stored procedure somewhere that can do this better, but here is a query that can do what you ask (put your column name in place of

ACCOUNTID

):

SELECT O.NAME as TBNAME, C.NAME AS COLNAME,
   T.NAME AS TYPE, C.PREC,           
   SUBSTRING(' 1234567',C.SCALE+1,1) AS DECIMALS,           
   SUBSTRING('        Y',C.STATUS+1,8) AS NULLS,
   C.COLID AS COLNO    
 FROM SYSOBJECTS O, SYSCOLUMNS C, SYSTYPES T    
 WHERE O.ID=C.ID
   AND C.USERTYPE=T.USERTYPE
   AND O.TYPE='U'
   AND C.NAME='

ACCOUNTID

'

(Please excuse the use of old-style join syntax, but this is from an old procedure.)

 

[Zathras]

See, I said there was an easier way.

Simultaneous posts are a real problem in this forum. If the system had informed me that another post has been made before accepting mine, I wouldn't have cluttered up the space. Terry, you seem to be one of the big dogs. Can you get them to improve the forum to help avoid duplicate responses?

 

[tlbroadbent]

Zathras,

I'm just a member like you. Anyone can send suggestions. Just click on the Contact Us link at the top of the page and then click on Feed Back.

It doesn't hurt to know different ways to perform various functions in SQL Server. I often use system tables as you did because using them is faster and more flexible for me. Microsoft recommends not using system tables but I'm a hard case. I think the Information_schema views and system stored procedures are easier for people to use when not well acquainted with the system tables. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[chrishall314]

Terry,

Thanks...your idea about using the information_schema views worked perfect.

Chris