`show create table` equivalent in oracle sql
If you are asking about SQL*Plus commands (show create table table_name doesn't appear to be a SQL statement), you can use the desc command
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
If you really want a SQL statement, you can use the dbms_metadata package
1 select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' )
2* from dual
SQL> /
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
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 FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
ALTER INDEX "SCOTT"."PK_EMP" UNUSABLE ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
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 FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CACHE
Depending on the tool you are using, you may need to run set long 10000 first, that tells SQL*Plus to display the first 10,000 bytes of any LOB that is selected. If your DDL is longer, set a larger value.
Check table exist or not before create it in Oracle
As Rene also commented, it's quite uncommon to check first and then create the table.
If you want to have a running code according to your method, this will be:
declare
nCount NUMBER;
v_sql LONG;
begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount <= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;
END IF;
end;
But I'd rather go catch on the Exception, saves you some unnecessary lines of code:
declare
v_sql LONG;
begin
v_sql:='create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
ELSE
RAISE;
END IF;
END;
/
How to get a SQL script of a table?
Try to spool the output of the below query,
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
Like,
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u WHERE TABLE_NAME = '<your_table>';
spool off;
Reference: http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm
show Create commands for table in SQL Management Studio (2008)
In Object Explorer, expand your server/database, expand Tables, right-click the table in question, and choose Script Table as > CREATE To > New Query Editor Window.
If you want to script multiple tables, you can turn on Object Explorer Details (F7 or from the View menu), highlight "Tables" on the left, then use Shift+ or Ctrl+ to select multiple tables in the right pane (just like you would select multiple files in Windows Explorer). Then you can do the same thing, right-click, Script Table as > ...
`show create table` equivalent in sap hana
Actually there are several ways to do that.
- SAP HANA Studio table editor (open the table definition) and click on the export as SQL icon.
- call
get_object_definition ('schema_name', '<object_name>') - Use the export table functionality - this will create a .SQL file with the definition.
How to get SHOW CREATE TABLE in BigQuery
BigQuery now supports a DDL column in INFORMATION_SCHEMA.TABLES view, which gives you the CREATE TABLE (or VIEW) DDL statement.
Note that the DDL column is hidden if you do SELECT * FROM I_S.TABLE, you need to query the view like:
SELECT
table_name, ddl
FROM
`bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
WHERE
table_name="population_by_zip_2010"
gives you
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_name | ddl |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| population_by_zip_2010 | CREATE TABLE `bigquery-public-data.census_bureau_usa.population_by_zip_2010` |
| | ( |
| | geo_id STRING OPTIONS(description="Geo code"), |
| | zipcode STRING NOT NULL OPTIONS(description="Five digit ZIP Code Tabulation Area Census Code"), |
| | population INT64 OPTIONS(description="The total count of the population for this segment."), |
| | minimum_age INT64 OPTIONS(description="The minimum age in the age range. If null, this indicates the row as a total for male, female, or overall population."), |
| | maximum_age INT64 OPTIONS(description="The maximum age in the age range. If null, this indicates the row as having no maximum (such as 85 and over) or the row is a total of the male, female, or overall population."), |
| | gender STRING OPTIONS(description="male or female. If empty, the row is a total population summary.") |
| | ) |
| | OPTIONS( |
| | labels=[("freebqcovid", "")] |
| | ); |
+------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Create a table in Oracle SQL If table does not exist using select and join
The issue was because of the static SQL data.
The table was created dynamically, but the rest of the statements were trying to access the static data.
To solve this, I have created two anonymous blocks, the first one having the create table statement, commit it and end the block.
The next anonymous block will have the rest of the statements to be executed after the IF clause.
This solved my problem.
DECLARE
count_matching_tbl BINARY_INTEGER := 0;
BEGIN
SELECT COUNT(*)
INTO count_matching_tbl
FROM dba_tables
WHERE LOWER(table_name) = 'testtable';
IF(count_matching_tbl = 0)
THEN
EXECUTE IMMEDIATE ( ' CREATE TABLE testtable AS (SELECT A.*, B.* from tab1 A JOIN tab2 B ON A.id = B.RID WHERE 1=2)');
COMMIT;
END IF;
END;
/
BEGIN
-- Rest of the database execution statements
COMMIT;
END;
/
how to create table based on multiple table in oracle
If the only duplicate names are from the join keys, then using can solve your problem:
CREATE TABLE schema_rr_dmt AS
SELECT *
FROM a_loans al INNER JOIN
s_CUSTRLTNP rl
USING (id_nasabah) INNER JOIN
s_customer cs
ON id_nasabah = rl.enterprise_id;
However, this doesn't work if there are other duplicate column names.
Related Topics
Tsql Select into Temp Table from Dynamic SQL
Return All Possible Combinations of Values Within a Single Column in SQL
How to Dynamically Use Tg_Table_Name in Postgresql 8.2
How to Bulk Update Sequence Id Postgresql for All Tables
Diagnosing an Oledb Exception When Quering Excel 2010
Normalizing Accented Characters in MySQL Queries
SQL Sort Order with Null Values Last
SQL Update Woes in Ms Access - Operation Must Use an Updateable Query
How to Pivot Dynamically with Date as Column
Is Natural (Join) Considered Harmful in Production Environment
Add a Column to a Table with a Default Value Equal to the Value of an Existing Column
SQL - How to Get Only the Numbers After the Decimal
Appending Data to a MySQL Database Field That Already Has Data in It
SQL Server Convert Select a Column and Convert It to a String
How to Find Out What Foreign Key Constraint References a Table in SQL Server