Customizing the Create FileTable Template in SQL Server 2012

In SQL Server 2012, the new FileTable feature provides some very interesting new capabilities (which I will blog about later). However, the SSMS GUI only provides a T-SQL script template to create a new FileTable. And while this template is very complete, it isn’t easy to use. There are more than 20 placeholders that need to be replaced, and you even have to enter the name of the database where the FileTable needs to be created (even though SSMS will select that database as the active database in the GUI).

You can modify the Create FileTable template though and already complete some of the placeholders if you know the values will not change often.

The template is a T-SQL script file located in the following folder (on an x64 system):

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Table\Create FileTable.sql

(That by the way is also the location of many other table-related script templates.)

By editing that file to your liking (I’ve included my edits below as an example), you can speed up the process of creating FileTables using script. You may want to create a backup copy of the original file for future reference first.

-- =========================================
-- Create FileTable template
-- (customized 2012-01-06, Sven Aelterman)
-- =========================================

IF OBJECT_ID('dbo.<table_name>', 'U') IS NOT NULL
  DROP TABLE dbo.<table_name>

--  WITH
--  (
--    FILETABLE_DIRECTORY = '<file_table_directory_name, sysname, sample_filetable>',
--    FILETABLE_COLLATE_FILENAME = <file_table_filename_collation, sysname, database_default>
--  )

Specifically, I removed the USE statement, commented out the options FILETABLE_DIRECTORY and FILETABLE_COLLATE_NAME and changed occurrences of the “schema_name” placeholder to “dbo”. Now, I only have 3 placeholders (“<table_name>”) that I need to replace. If I want to change the options, I can just uncomment that section and replace some additional placeholders with valid values.

Let me know what you think, or ask a question...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.