Find any instance of a field in all table.

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/

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.

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

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/

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

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/

cardgunner

326 Posts

Posted - 2008-01-29 : 14:34:24

I tired the id and that is a number.

Card Gunner

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

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.

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources