Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Find any instance of a field in all table.

Author  Topic 

cardgunner

326 Posts

Posted - 2008-01-29 : 10:26:56
My db has about 8000 tables. I'm not joking or exaggerating. Each table has 7 copies. Each copy has a different 3 digit ending. We use 2 of those tables for live. and 2 Tables for training.

I want to find all the tables that have a certain field name.

I was told that I had to open each table up individually.

I don't want to accept that.

So if I wanted to find all the tables where field MFG is, is there a easier amd more efficient way? I need to do updates and it is starting to look impossible.

I'm real new to all of this and trying to learn this thru a book and this forum so any help would be great.

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-29 : 12:13:04
Query syscolumns. check out books online for column descriptions..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-29 : 12:18:53
quote:
Originally posted by dinakar

Query syscolumns. check out books online for column descriptions..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/



Make things easy on yourself and query INFORMATION_SCHEMA.COLUMNS.
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-01-29 : 13:00:08
Thanks jdaman.

I looked it up and tried running the statement as they have it

Select TABLE_CATALOG = Left(TABLE_CATALOG, 10),
TABLE_SCHEMA = Left(TABLE_SCHEMA, 10),
TABLE_NAME = Left(TABLE_NAME, 10),
COLUMN_NAME = Left(COLUMN_NAME, 20)
From pubs.Information_Schema.Columns
Where Table_Name = 'authors'


Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'pubs.Information_Schema.Columns'.


I'm using SQL Server 2000. Does it make a differnce.

Card Gunner
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-29 : 13:03:43
quote:
Originally posted by cardgunner

Thanks jdaman.

I looked it up and tried running the statement as they have it

Select TABLE_CATALOG = Left(TABLE_CATALOG, 10),
TABLE_SCHEMA = Left(TABLE_SCHEMA, 10),
TABLE_NAME = Left(TABLE_NAME, 10),
COLUMN_NAME = Left(COLUMN_NAME, 20)
From pubs.Information_Schema.Columns
Where Table_Name = 'authors'


Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'pubs.Information_Schema.Columns'.


I'm using SQL Server 2000. Does it make a differnce.

Card Gunner



Yes. Information_Schema views are in 2005.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-01-29 : 13:32:19
Well I got this

select * from mydb.dbo.syscolumns
where name='t_mfg'


and it returned 437 t_mfg.

Is there something I can add to tell me what the table names are?

Card Gunner
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-29 : 13:55:01
You can get the table name via object_name(id) or join with sysobjects.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-01-29 : 14:34:24
I tired the id and that is a number.

Card Gunner
Go to Top of Page

cardgunner

326 Posts

Posted - 2008-01-29 : 14:47:49
Got it!!!

So excited.

Thank you for helping me out.

select *
from mydb.dbo.sysobjects join
mydb.dbo.syscolumns on mydb.dbo.sysobjects.id=mydb.dbo.syscolumns.id
where mydb.dbo.syscolumns.name='t_mfg' and
mydb.dbo.sysobjects.name like '%100'


This is so cool. Hours and hours saved.


THANK YOU!

Card Gunner
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-29 : 15:32:55
quote:
Originally posted by dinakar

Yes. Information_Schema views are in 2005.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/



INFORMATION_SCHEMA views are in SQL 2000:
http://support.microsoft.com/kb/q294350/

The reason his code gave him an error was INFORMATION_SCHEMA views are stored in the master database. He was looking for pubs.INFORMATION_SCHEMA which does not exist. The referenced article shows the correct way to use the views.

Go to Top of Page

cardgunner

326 Posts

Posted - 2008-01-29 : 16:18:00
Thanks. This could work too. I haven't spent anytime making this fit but is either better then the other?

Card Gunner
Go to Top of Page

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-29 : 16:23:09
quote:
Originally posted by cardgunner

Thanks. This could work too. I haven't spent anytime making this fit but is either better then the other?

Card Gunner



Its all a matter of the situation and your personal preference. I believe the sys tables have more detailed information however the INFORMATION_SCHEMA views are easier to "read". Due to laziness, whenever possible, I prefer to use the views.
Go to Top of Page
   

- Advertisement -