Blending Notes: Oracle 11g & SQL Server 2012-The structure of a table

There are multiple ways of retrieve table information. Few days ago I was desperately looking for an Equivalent Command to Oracle command of 'DESC' OR 'DESCRIBE' in SQL SERVER because I badly wanted to get some information about an existing database. This post mainly focus on describing MS SQL Server table using sp_columns and ‘DESCRIBE’ command of Oracle which retrieves column information for the specified table.

For an instance, Employee table includes information like table name, table owner, details about columns, and its physical storage size information referred to as metadata.

These kind of information of a table can retrieves by using the DESCRIBE command of Oracle.

Syntax- Oracle:

• ¿       DESC[RIBE] <SCHEMA>.tablename

DESCRIBE key word can be shortened to DESC and schema can be omitted. 

Describing EMPLOYEE table using DESCRIBE command in Oracle 

 Syntax- SQL SERVER:

•      EXEC sp_columns @table_name = ‘EMPLOYEE’;

Above catalog stored procedure returns column information for the EMPLOYEE table also has ability to specifically mention the object owner of the table as well. If you want to retrieve only one column of catalog information you can simply specified as follows,

Multiple ways ;

¿  

•          exec sp_columns Employee;

•     exec sp_columns Employee, @column_name='%ID';

•    exec sp_columns @table_owner = 'HumanResources', @table_name = 'Employee' @column_name='LoginID';

sp_tables also one of another important stored procedure which returns a list of objects

Below mentioned stored procedure returning a list of objects that can be queried.

•        EXEC sp_tables;
•       EXEC sp_tables  @table_name = 'Employee';

Enjoy learning !