File tables are not scripted properly

JosvanDuijn

When you compare a database with file tables (new SQL 2012 feature) they are not scripted properly!

I tested this with version 10.1.10.102.

How it is scripted now:

CREATE TABLE [dbo].[BOOKING_INVOICE_FILE]
(
[stream_id] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF__BOOKING_I__strea__2C3393D0] DEFAULT (newsequentialid()),
[file_stream] [varbinary] (max) FILESTREAM NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[path_locator] [sys].[hierarchyid] NOT NULL CONSTRAINT [DF__BOOKING_I__path___2D27B809] DEFAULT (convert(hierarchyid, '/' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/')),
[parent_path_locator] AS (case when [path_locator].[GetLevel]()=(1) then NULL else [path_locator].[GetAncestor]((1)) end) PERSISTED,
[file_type] AS (getfileextension([name])) PERSISTED,
[cached_file_size] AS (datalength(file_stream)) PERSISTED,
[creation_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__BOOKING_I__creat__2E1BDC42] DEFAULT (sysdatetimeoffset()),
[last_write_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__BOOKING_I__last___2F10007B] DEFAULT (sysdatetimeoffset()),
[last_access_time] [datetimeoffset] NULL CONSTRAINT [DF__BOOKING_I__last___300424B4] DEFAULT (sysdatetimeoffset()),
[is_directory] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_di__30F848ED] DEFAULT ((0)),
[is_offline] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_of__31EC6D26] DEFAULT ((0)),
[is_hidden] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_hi__32E0915F] DEFAULT ((0)),
[is_readonly] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_re__33D4B598] DEFAULT ((0)),
[is_archive] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_ar__34C8D9D1] DEFAULT ((1)),
[is_system] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_sy__35BCFE0A] DEFAULT ((0)),
[is_temporary] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_te__36B12243] DEFAULT ((0))
CONSTRAINT [UQ__BOOKING___9DD95BAF514888B4] UNIQUE NONCLUSTERED ([stream_id])
)
GO

How it should be scripted:


CREATE TABLE [dbo].[BOOKING_INVOICE_FILE] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [Supose_Documents]
WITH
(
FILETABLE_DIRECTORY = N'BOOKING_INVOICE_FILE', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS
)

GO

James B

Thanks for your post. Unfortunately SQL Compare doesn't yet work with many of the new keywords and functions in SQL 2012.

I've logged a change-request under ref SC-5769 for this one.