Rick Caminiti dotCOM

First of all, when I’m trying to find metada in Oracle, I am usually using Toad, and have no problem finding what I need because it’s such a great too. However, even in SQL-Plus, it’s still really easy to find what I need by querying the data dictionary:


select *
from all_columns
where owner = user
and table_name = 'tablename'

… Or even easier, if it’s in the schema I’m already in:

select *
from user_columns
where table_name = 'tablename'


It seems that it’s still pretty easy in SQL Server to do this, but off the bat, it’s not exactly as intuitive. Apparently you can go through sysobjects, but even Microsoft suggests against it, considering they reserve the right to change any and all system tables from version to version.

There are alternatives though; views in INFORMATION_SCHEMA:
For instance, to view tables:

select *
from INFORMATION_SCHEMA.TABLES

… And for columns:

select *
from INFORMATION_SCHEMA.Columns
where table_schema = 'schemaname'
and table_name = 'tablename'


Here’s some more great information about INFORMATION SCHEMA: Here