How to detect if a table is a FileTable in SQL Server

Question

I am working with an existing SQL script which drops all foreign keys from a database. This runs into trouble if one of the tables is a FileTable.

The primary question: is there a way to detect that a particular table is a FILETABLE and skip dropping the foreign keys on that table?

If that is possible: is it also possible to get even more granular and drop any non-system generated foreign keys from the FILETABLE by differentiating between system foreign keys and custom foreign keys?

DECLARE @fkdel varchar(512);

DECLARE FkCrsr CURSOR FOR 
     SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME +']' 
     FROM information_schema.table_constraints WITH (NOLOCK)
     WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

OPEN FkCrsr;

FETCH NEXT FROM FkCrsr INTO @fkdel;

WHILE @@FETCH_STATUS = 0
BEGIN;
    PRINT @fkdel;
    EXEC (@fkdel);

    FETCH NEXT FROM FkCrsr INTO @fkdel;
END;

CLOSE FkCrsr;
DEALLOCATE FkCrsr;

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";

Running this on a database containing a FileTable results in an error like

Msg 3865, Level 16, State 1, Line 3
The operation on object 'FK__DocumentS__paren__3A69DAC6' is blocked. The object is a FileTable system defined object and user modifications are not allowed.

Msg 3727, Level 16, State 0, Line 3
Could not drop constraint. See previous errors.

score 2 · Accepted Answer · answered Nov 05 '19 at 19:40

You should not be using the information schema views for this. Especially since you care about the schema. The MS documentation even states not to use this. https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/table-constraints-transact-sql?view=sql-server-ver15

If you instead use the system views this becomes a lot simpler. For example sys.tables has a column "is_filetable". https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-ver15 Also notice I am using QUOTENAME here instead of manually adding square brackets.

Then you can query sys.foreign_keys to find all your foreign keys since that is the only type of constraint you are concerned with.

Your whole looping construct could be simplified to something like this.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
from sys.foreign_keys fk
join sys.tables t on t.object_id = fk.parent_object_id
join sys.schemas s on s.schema_id = t.schema_id
where t.is_filetable = 0

select @SQL

--uncomment the line below to execute your dynamic sql
--exec sp_executesql @SQL

That takes us to sp_msforeachtable. It is rarely a good idea to use undocumented procedures like this. I would also be a bit nervous that you are turning off every constraint on every table instead of just the ones you removed. Maybe instead you should capture all the tables you are going to drop foreign keys from (before you drop them) and then disable all the constraints on ONLY those tables.

How to detect if a table is a FileTable in SQL Server

1 Answers1