RegEx - How to select all text in between backes (multiline)

Question

Basically, what i want to do is to parse a *.sql file and select all CREATE TABLE Statements. Example below:

-- ----------------------------
-- Table structure for aes_interval
-- ----------------------------
DROP TABLE IF EXISTS `aes_interval`;
CREATE TABLE `aes_interval`  (
  `processcode` bigint(20) NOT NULL,
  `overstaying` int(11) NOT NULL,
  `floating` int(11) NOT NULL,
  PRIMARY KEY (`processcode`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci;

by running a RegEx that would select all in between (...) i will be able to get an output/substringed text like below:

CREATE TABLE `aes_interval`  (
  `processcode` bigint(20) NOT NULL,
  `overstaying` int(11) NOT NULL,
  `floating` int(11) NOT NULL,
  PRIMARY KEY (`processcode`) USING BTREE
)

i've tried CREATE TABLE\w+\((.|\n)*?\) but it only returns the output below:

CREATE TABLE `aes_interval`  (
  `processcode` bigint(20)

hopefully, i can pick up the proper regex here.

Answer 1

Assuming the CREATE TABLE statements are terminated by semicolon, and therefore that semicolon does not appear until the end of each statement, then the following regex should work:

(CREATE TABLE.*?;)

There is a caveat that the above would need to run in a tool/language with dot configured to match newline. And also multiline match mode would need to be enabled.

Demo

Edit:

I suspect that lazy dot is not working on Sublime Text. If so, then you can try the following pattern:

(CREATE TABLE[^;]*)