SQL Question - Querying the DB objects

[Ruprect]

Did a quick google and came up with little so I thought I'd ask you lot seeing as you all love showing off your skills

How can I find a table/view that contains a column that I know the name of?

e.g. I know the name of a column is "ISBN", how can I find, using a query, the table "Book"?

Know what I mean?

If possible, I would like to be able to do this across the whole DBMS, not just one database within it.

Over to you gurus

 

[BrilloPad]

select o.name, c.id, c.name
from syscolumns c, sysobjects o
where c.id=o.id
and c.name = "ISBN"

 

[TheFaQQer]

Give us a clue - what kind of database are you working with??????

SELECT table_name
FROM   dba_tab_columns
WHERE  column_name = 'ISBN'

will do it in Oracle for you.

 

[Ruprect]

Probably should have mentioned that! Sybase.

 

[Ruprect]

select o.name, c.id, c.name
from syscolumns c, sysobjects o
where c.id=o.id
and c.name = "ISBN"

That's the ticket. Thanks BP.

 

[TheFaQQer]

Probably should have mentioned that! Sybase.

Sorry, I thought you said it was a database....

 

[oracleslave]

Sorry, I thought you said it was a database....

 

[Bumfluff]

Off the top of my head its the syscolumns and sysobjects tables you need to join and query. The command sp_tables will list the tables you can view including system OR if your using Rapid SQL just use the 'Search DB option' under 'Tools'.

Sybase is sh*t, current gig is Sybase 6 months and just got another 6 months but Im seriously considering looking else soley because 12 months Sybase in line of work and not Oracle will make getting a new contract very hard.

 

[Sir_Edward_Matheson]

Sorry, I thought you said it was a database....

an RDBMS

 

[Ruprect]

Sorry, I thought you said it was a database....

lol, well, I could have said SQL Server...