What Is the Equivalent of 'Describe Table' in SQL Server

How to describe table in SQL Server 2008?

According to this documentation:

DESC MY_TABLE

is equivalent to

SELECT column_name "Name", nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type" FROM
user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';

I've roughly translated that to the SQL Server equivalent for you - just make sure you're running it on the EX database.

SELECT column_name AS [name],
       IS_NULLABLE AS [null?],
       DATA_TYPE + COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
                                  THEN 'Max'
                                  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                  END + ')', '') AS [type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'EMP_MAST'

Describe table structure

sp_help tablename in sql server -- sp_help [ [ @objname = ] 'name' ]

desc tablename in oracle -- DESCRIBE { table-Name | view-Name }

How to Describe table in SQL Server 2014

In SSMS find table you need, right-click on it, choose Script Table As... -> CREATE To -> New Query Editor Window

That will give you creation script where you can see all details about table structure.

Another way is to use system tables:

USE MyDB

SELECT  c.name,
        s.name,
        c.max_length,
        c.is_nullable
FROM sys.columns c
LEFT JOIN sys.systypes s
    ON s.xusertype= c.system_type_id
WHERE object_id = object_id(N'Employees')

This will give you:

name            name        max_length  is_nullable
EmployeeID      int         4           0
LastName        nvarchar    40          0
FirstName       nvarchar    20          0
...etc

Or views:

USE MyDB

SELECT  TABLE_NAME, 
        COLUMN_NAME, 
        DATA_TYPE,
        IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'Employees';

Output:

TABLE_NAME  COLUMN_NAME DATA_TYPE   IS_NULLABLE
Employees   EmployeeID  int         NO
Employees   LastName    nvarchar    NO
Employees   FirstName   nvarchar    NO
Employees   Title       nvarchar    YES

Note: also you can use EXEC sp_help 'Employees' more about this SP you can read on MSDN.

desc table in SQL Server?

dont use schema dbo.

exec sp_columns [ATRESMEDIA Resource Time Registr_];

why? because, following are the parameters accepted by sp_columns stored proc:

sp_columns [ @table_name = ] object

[ , [ @table_owner = ] owner ]

[ , [ @table_qualifier = ] qualifier ]

[ , [ @column_name = ] column ]

[ , [ @ODBCVer = ] ODBCVer ]

source: msdn

update:
Martin's explanation as in comment:

Strings in SQL Server are delimited by single quotes - as a parameter to a stored proc in very limited circumstances it will allow you to skip the quotes but the dot breaks that. exec sp_columns 'dbo.[ATRESMEDIA Resource Time Registr_]'; wouldn't give the syntax error - but that wouldn't be what the proc expects anyway as the schema would need to be the second param

Oracle and/or SQL Server equivalent to MySQL's DESCRIBE [table]?

In Oracle use

select dbms_metadata.get_ddl('TABLE','DEMO_ORDERS') from dual;

and it will return something like

  CREATE TABLE "OWNER"."DEMO_ORDERS" 
   (    "ORDER_ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "ORDER_TOTAL" NUMBER(8,2), 
    "ORDER_TIMESTAMP" DATE, 
    "USER_ID" NUMBER, 
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE, 
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID")
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

You can use various settings to filter in or out bits you don't want.

begin
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
       'SQLTERMINATOR',true);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 
       'SEGMENT_ATTRIBUTES',false);
end;

will give

  CREATE TABLE "OWNER"."DEMO_ORDERS" 
   (    "ORDER_ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "ORDER_TOTAL" NUMBER(8,2), 
    "ORDER_TIMESTAMP" DATE, 
    "USER_ID" NUMBER, 
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE, 
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID")
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE
   ) ;

Can DESCRIBE syntax be embedded in SELECT statement?

You can use COLUMNS table of INFORMATION_SCHEMA to get expected result as an alternate solution of DESCRIBE table option.

Try this:

SELECT COLUMN_NAME AS `Field`, COLUMN_TYPE AS `Type`, IS_NULLABLE AS `NULL`, 
       COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS `Extra`
FROM information_schema.COLUMNS  
WHERE TABLE_SCHEMA = 'schemaName' AND TABLE_NAME = 'table1';