Gathering Database Metadata On Oracle and Microsoft SQL Server

Sometimes you need the metadata of your database such tables, databases or columns via SQL. The DBMSs support to give this information. I described how to getmetadata of your databases via SQL:

Kimi zaman veritabanının metadatasına ihtiyaç duyabilrsiniz. Bu işlemi Oracle ve MSSQLServer ile nasıl yapılacağını aşağıdaki SQL cümleleri ile belirtmeye çalıştım:

On Microsoft SQL Server:
--List Databases
exec sp_databases

USE Pubs
--List Tables
SELECT *
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = 'authors'


--List Columns
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'authors'

On Oracle
--List Tables
SELECT *
FROM   ALL_TABLES a_t
WHERE  a_t.owner = 'HR'

--List Columns
SELECT *
FROM   ALL_TAB_COLUMNS atc
WHERE  atc.owner = 'HR' AND
           atc.TABLE_NAME = 'JOBS'
ORDER  BY atc.TABLE_NAME, atc.COLUMN_ID


Oracle gives database metadata more detailly. There is also a supplied package called DBMS_METADATA to give full functionality to deal with metada.