How to Get Database Information's

Article helps to show basic queries to retrieve database related information's.

Oracle

Version

PL/SQL, TNS versions using with Oracle.

SELECT * FROM v$version;

Which version of oracle you are running.

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

Or, in more readable way.

SELECT * FROM product_component_version;

Instance

Displays the state of the current instance.

SELECT * FROM v$instance;

About license limits of the current instance.

SELECT * FROM v$license;

Database

Db Name.

SELECT * FROM GLOBAL_NAME

Db IP Address.

SELECT UTL_INADDR.get_host_address FROM dual

Db Host Name.

SELECT UTL_INADDR.GET_HOST_NAME('above ip address') FROM dual

Client

Client IP Address.

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual

Db Host Name

SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual

Db Host Name with domain.

SELECT SYS_CONTEXT('USERENV','HOST') FROM dual

Current Client session details who using DB.

SELECT * FROM v$session WHERE username = 'User/Schema name'

To which DB user connected to.

SELECT SUBSTR(GLOBAL_NAME, 1, INSTR(GLOBAL_NAME,'.')-1) FROM GLOBAL_NAME

Show all Function/Procedure/Table/View ECT

SELECT * FROM ALL_SOURCE WHERE OWNER = 'User/Schema name' ORDER BY TYPE;

SELECT * FROM user_objects

Tables

all_tables - Lists the tables which you have the access.

user_tables - List the tables in your schema.

dba_tables - lists all tables from all schema including system tables.

Tables which includes views also

All_all_tables describes the object tables and relational tables accessible to the current user

SELECT * FROM all_tables WHERE OWNER = 'User/Schema name'

SELECT * FROM user_tables

SELECT * FROM dba_tables

SELECT * FROM tab

SELECT * FROM ALL_ALL_TABLES;

Find table column information's

SELECT * FROM all_tab_cols WHERE table_name = 'table name'

Any table comments

SELECT * FROM all_tab_comments WHERE table_name = 'table name'

SQL Server

Version

Which versions of Sql sever you are running.

SELECT @@VERSION

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('edition')

SERVERPROPERTY è Returns property information about the server instance.

For more ServerProperty details: http://msdn.microsoft.com/en-us/library/ms174396.aspx

Client

Client details (IP Address, Machine Name, Instance using).

SELECT con.client_net_address as IPAddress,

sess.host_name as MachineName, sess.program_name as ApplicationName,

login_name as LoginName

FROM sys.dm_exec_connections con

inner join sys.dm_exec_sessions sess

on con.session_ID=sess.session_ID

WHERE con.session_ID = @@SPID

Show all Function/Procedure/Table/View ECT

select distinct type, name from sysobjects

Tables

EXEC sp_help 'temp'

Select * From Information_Schema.TABLES

Select * From Information_Schema.columns