Simulate a table creation with SQL

Question

Hello.

Is there a standard way to simulate a table creation in a database by using SQL? I don't want the table to be created, just check if it could be created. One way would be to create it and then delete it again. Any other way?

Answer 1

Depends on the SQL DBMS you're interested in. For example Postgres supports transactional DDL and the following will work:

START TRANSACTION;
CREATE TABLE ... ();
<check for error here>
ROLLBACK;

Answer 2

Most major servers support transactional DDL, so you can do something along these lines:

begin transaction

create table Foo ...

rollback transaction

Theoretically, in case of error it should be reported back to client, but table will not be created altogether.

Answer 3

If you're using MySQL, you could create it using a transient storage engine, like MEMORY .

Answer 4

Really, you have to actually create it to make sure everything is OK.

Foreign key references, functions used as default or check constraints or in computed columns are not checked until execute time.

Answer 5

One basic method (SQL Server) is to use "SET FMTONLY ON".

Useful for checking the statement is valid, though won't tell you everything (e.g. if the table already exists).

This will succeed:

SET FMTONLY ON
EXECUTE ('CREATE TABLE SomeTable(SomeField INTEGER)')
SET FMTONLY OFF

This will not:

SET FMTONLY ON
EXECUTE ('CREATE TABLE SomeTable(dodgysyntax)')
SET FMTONLY OFF

This approach is probably more useful for SELECT statements, which is what I've used it for in the past. It doesn't actually execute the statement, but returns out the metadata.