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