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>
GO

CREATE TABLE dbo.<table_name> AS FILETABLE
--  WITH
--  (
--    FILETABLE_DIRECTORY = '<file_table_directory_name, sysname, sample_filetable>',
--    FILETABLE_COLLATE_FILENAME = <file_table_filename_collation, sysname, database_default>
--  )
*/
GO

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.