This is a very common task that most application/database developers come across;  testing various datatypes of a database. Honoring the principle DRY, here is a template with  ddl for table creation (covering most data types) across various databases.

Oracle:

CREATE TABLE all_types(c_smallint         SMALLINT,

c_integer            INTEGER,

c_real           REAL,

c_double         DOUBLE PRECISION,

c_bigdecimal     DECIMAL(13,0),

c_varchar2         VARCHAR2(254),

c_nvarchar2         NVARCHAR2(254),

c_varchar        VARCHAR(254),

c_char        CHAR(254),

c_nchar        NCHAR(254),

c_number       number(3,2),

c_long          LONG,

c_raw           RAW(2000),

c_interval_y_m INTERVAL YEAR (3) TO MONTH,

c_interval_d_s INTERVAL DAY (3) TO SECOND,

c_date       DATE,

c_clob            CLOB,

c_blob            BLOB,

c_bfile           BFILE)

CTAS: Example of Create Table As in Oracle

create table all_types1 as select * from all_types where 1=1; (use 1=1 to create table with data and 1=0 to create table without data)

Teradata:

CREATE TABLE MYDB.all_datatypes

(

date_ DATE FORMAT 'YY/MM/DD',

byte_ BYTE(30),

VARBYTE_ VARBYTE(30),

char_ CHAR(30),

varchar_ VARCHAR(30) ,

long_varchar_ LONG VARCHAR ,

a3 VARCHAR(30) ,

byteint_ BYTEINT,

decimal_ DECIMAL(5,2),

float_ FLOAT,

integer_ INTEGER,

smallint_ SMALLINT,

interval_y_m INTERVAL YEAR(2) TO MONTH,

interval_y INTERVAL YEAR(3),

interval_m INTERVAL MONTH(2),

blob_ BLOB(2048),

numeric_ DECIMAL(3,2));

CTAS: Example of Create Table As sql command in Teradata

create table mydb.RVSE_all_datatypes5  as ( select * from mydb.all_datatypes2 ) with no data;

Teradata is available for developer/sandbox environments at http://www.teradata.com/t/teradata-express/ both as VM that runs on VMWare Player as well as an Amazon EC2 instance that runs Teradata instance on the Amazon cloud.