MS SQL - Export Database as SQL >> SQL Export >> Tables Create - Produce wrong syntax

MrHIDEn's profile image MrHIDEn posted 7 years ago in General Permalink

Hi, When you try to export database's tables, HEIDISQL produces wrong syntax like:

CREATE TABLE IF NOT EXISTS "SomeTable" (
    "Id" INT(10,0) NOT NULL,
    "Big" BIGINT(19,0) NULL DEFAULT NULL,
    PRIMARY KEY ("Id")
);

But it should be:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE TYPE = N'U' AND name = N'SomeTable')
CREATE TABLE "SomeTable" (
    "Id" INT NOT NULL,
    "Big" BIGINT NULL DEFAULT NULL,
    PRIMARY KEY ("Id")
);

I only have found those issues but there could be more of them at creating data table.

Best regards

MrHIDEn's profile image MrHIDEn posted 7 years ago Permalink

Update, should be:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE TYPE = N'U' AND name = N'SomeTable')
CREATE TABLE "SomeTable" (
    "Id" INT IDENTITY(1,1) NOT NULL,
    "Big" BIGINT NULL DEFAULT NULL,
    PRIMARY KEY ("Id")
);
[expired user #10562]'s profile image [expired user #10562] posted 7 years ago Permalink

Also, DATETIME must not have a length specifier (same as INT, and BIGINT). And finally, MS SQL has a thousand row limit for inserts -- the KB option does not make any sense here.

I hope someone will find the time to fix this some day, as this is really annoying :-)

MrHIDEn's profile image MrHIDEn posted 7 years ago Permalink

Wrong:

CREATE TABLE IF NOT EXISTS "SomeTable" (
    "Id" INT(10,0) NOT NULL COMMENT 'ColumnDescription1',
    "Big" BIGINT(19,0) NULL DEFAULT (NULL),
    "Date" DATE(0) NOT NULL DEFAULT (NULL),
    "DateTime" DATETIME(3) NULL DEFAULT (getdate()),
    PRIMARY KEY ("Id")
);
COMMENT='TableDescription1';

Right:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE TYPE = N'U' AND name = N'SomeTable')
CREATE TABLE "SomeTable" (
    "Id" INT IDENTITY(1,1) NOT NULL,
    "Big" BIGINT NULL DEFAULT (NULL),   
    "Date" DATE NOT NULL DEFAULT (NULL),
    "DateTime" DATETIME NULL DEFAULT (getdate()),
    PRIMARY KEY ("Id")
);
EXECUTE sp_addextendedproperty 'MS_Description', 'TableDescription1', 'Schema', 'dbo', 'table', 'SomeTable';
EXECUTE sp_addextendedproperty 'MS_Description', 'ColumnDescription1', 'Schema', 'dbo', 'table', 'SomeTable', 'column', 'DateTime';

Please login to leave a reply, or register at first.