Memory madness - SQL Server Forums

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

Transact-SQL (2000)

Memory madness

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-16 : 12:27:20

I'm sure that someone has asked this before but can't find it!!

My database has many tables that have the same field name for one field (foreign key for most of them). What I want to know is where all these instances are. So for example if I have

Table A with fields ID1, name,...
Table b with fields ID2, date,..
Table c with fields ID1, time,...

and I am looking for the field called ID1 then the query returns

Table A
Table c
...

many thanks

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 13:06:49

will this help?

select *
from information_schema.columns
where column_name = 'id1'

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-16 : 13:26:47

Will this do it?


Select Distinct A.name 
From <yourDb>.dbo.sysobjects A
Inner Join <yourDb>.dbo.syscolumns B
On A.id = B.id
Where A.xType='U'
and B.name = 'ID1'

EDIT: See what happens when I get interrupted with work...

Corey

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-16 : 17:57:18

Thanks guys I'll try those in the morning (he says finding it rather sad that he is on here at 11pm - and stone cold sober too)

steve

Steve no function beer well without

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-17 : 04:43:51

Interestingly enough these gave very different results.

the first one returned 138 rows and the latter 49. The first one included views which I was hoping to be able to ignore but would be useful for something else

Thanks to both

steve

Steve no function beer well without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 05:11:39

well as long as you got what u needed... did u?

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-17 : 16:59:01

i really need to study the information_schema stuff as I know nothing about it... except what i've seen here in passing...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 18:15:15

well BOL is a good place to start.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-09-18 : 07:01:51

quote:
Originally posted by elwoos

The first one included views which I was hoping to be able to ignore but would be useful for something else


SELECT C.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
	JOIN INFORMATION_SCHEMA.TABLES T
		 ON T.TABLE_CATALOG = C.TABLE_CATALOG
		AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
		AND T.TABLE_NAME = C.TABLE_NAME
WHERE	    T.TABLE_TYPE = 'BASE TABLE'
--WHERE	    T.TABLE_TYPE = 'VIEW'
	AND C.COLUMN_NAME = 'id1'

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-21 : 18:03:30

Cheers Kristen, I'll try that.

btw - don't fancy a job swap for a few weeks do you?

steve

Steve no function beer well without

Kristen
Test

22859 Posts

Posted - 2004-09-22 : 13:18:24

You said it was too far for you to relocate here ... let alone commute!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources