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

Question

I'm looking to find out what kind of 'create table' statement I would need to create a given table from scratch. I'm especially interested in primary key constraints, unique constraints, foreign key constraints and column names.

How would I do this in Oracle and/or SQL Server?

Oddly enough, the only connection I have to the database is an ODBC connection from a Linux box. Though for Oracle I can almost certainly get SQL*Plus up and running.

Answer 1

In SQL Server the easiest would be right clicking on the table-->Script Table as--> CREATE TO--> New query window. and it will create something like this, I picked the HumanResources.EmployeeAddress table in the AdventureWorks database

USE [AdventureWorks]
GO

/****** Object:  Table [HumanResources].[EmployeeAddress]    Script Date: 09/01/2010 20:05:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [HumanResources].[EmployeeAddress](
    [EmployeeID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeAddress_EmployeeID_AddressID] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC,
    [AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key. Foreign key to Employee.EmployeeID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'COLUMN',@level2name=N'EmployeeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key. Foreign key to Address.AddressID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'COLUMN',@level2name=N'AddressID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'COLUMN',@level2name=N'rowguid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cross-reference table mapping employees to their address(es).' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'CONSTRAINT',@level2name=N'PK_EmployeeAddress_EmployeeID_AddressID'
GO

ALTER TABLE [HumanResources].[EmployeeAddress]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeAddress_Address_AddressID] FOREIGN KEY([AddressID])
REFERENCES [Person].[Address] ([AddressID])
GO

ALTER TABLE [HumanResources].[EmployeeAddress] CHECK CONSTRAINT [FK_EmployeeAddress_Address_AddressID]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing Address.AddressID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'CONSTRAINT',@level2name=N'FK_EmployeeAddress_Address_AddressID'
GO

ALTER TABLE [HumanResources].[EmployeeAddress]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeAddress_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [HumanResources].[Employee] ([EmployeeID])
GO

ALTER TABLE [HumanResources].[EmployeeAddress] CHECK CONSTRAINT [FK_EmployeeAddress_Employee_EmployeeID]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing Employee.EmployeeID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'CONSTRAINT',@level2name=N'FK_EmployeeAddress_Employee_EmployeeID'
GO

ALTER TABLE [HumanResources].[EmployeeAddress] ADD  CONSTRAINT [DF_EmployeeAddress_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of NEWID()' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'CONSTRAINT',@level2name=N'DF_EmployeeAddress_rowguid'
GO

ALTER TABLE [HumanResources].[EmployeeAddress] ADD  CONSTRAINT [DF_EmployeeAddress_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'EmployeeAddress', @level2type=N'CONSTRAINT',@level2name=N'DF_EmployeeAddress_ModifiedDate'
GO

There is also sp_help but it doesn't give you a script

sp_help 'HumanResources.EmployeeAddress'

Programatically you can use SQL Server Management Objects (SMO) from .NET or SSIS

Answer 2

ok - I'm a bit confused. Your title says one thing, and your question says another.

For your question in the title:

Oracle: use "describe table"

SQL Server sp_columns table or sp_help table for more info.

For your written question:

Oracle: http://www.adp-gmbh.ch/ora/sql/create_table.html

SQL Server: http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Answer 3

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
   ) ;