In my previous article, we discussed about how to enable FileStream and File table in SQL Server 2012. Today, we will learn how to perform Data Definition Language (DDL) Statements in file table.
- Create File table :
Given below is the script to create file table :
USE MyFileTableDB GO CREATE TABLE DataBank AS FileTable WITH ( FileTable_Directory = 'MyFileTable_Dir' );
Once file table is created successfully, you can execute given below script to check whether the file table is created or not.
select * from sys.filetables where object_id=object_ID('databank')
- Alter File table:
In the above file table, I wrongly spelled the FileTable_Directory name. So, can we alter file table and correct FileTable_Directory name?
Given below is the script to alter file table.
ALTER TABLE DataBank SET ( FILETABLE_DIRECTORY = N'MyFileTable_Directory');
Once file table is altered, you can execute the given below script to verify.
select * from sys.filetables where object_id=object_ID('databank')
- Truncate File table:
If you want to truncate the file table, given below is the script but it will delete all data files in the file table (be careful).
TRUNCATE TABLE databank
- Drop File table:
If you want to drop the file table, given below is the script but it will also drop all the data files associated with this files table (be careful).
DROP TABLE databank
Tomorrow I shall post how to perform Data Manipulation Language (DML) Statements to file table.
SQL Server Portal 