How to find whether the SQL query type is DML or DDL?

Question

How can you find whether the SQL query type is DDL or DML?

Answer 1

Classically, the DML statements are:

• SELECT
• INSERT
• DELETE
• UPDATE
• MERGE (newcomer on the block)

Anything else is DDL - according to some sets of definitions.

Some of the 'other statements' are more like 'session control' statements; not really DML, not really DDL.

If you wish to detect these statements, you can either prepare (and describe) the statement and look at the returned information to diagnose whether it is one of the DML statements listed above, or you can scan for these keywords as the first non-comment words in the statement. This covers the vast majority of practical cases. What you do if you have a single string with multiple statements (possibly of different types) in them is a decision you'll have to make on your own. Not all DBMS allow that anyway.

Answer 2

DDL stands for Data Definition Language. Any statement that will cause a change in the data definitions is a DDL statment. The usual confusion is with the INSERT, UPDATE an DELETE (and as Jonathan mentions-MERGE) statements. Even though they add data to the tables, they don't change their structure or add anything to the data definitions. Looking at it another way, DDL statements usually add new records/information to the data dictionary (with the exception of query statistics). This, however, need not be true always.

Anything that is not DDL is DML which stands for Data Manipulation Language.

Answer 3

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency