SQL Server Portal

I received a request to add a column in the entire table of the database for a customer. It was so simple that I made the script as given below and told them to run it first and then again run its result set in SQL server, so all tables would be altered. It appeared that the Job was done but unfortunately it was not. One thing I forgot to mention is that the client environment in SQL Server 2012. If you look at the query it will perform alter table in all user table.

Select 'Alter Table ' + quotename(schema_name([schema_id])) +'.' + quotename([name])
+ ' ADD [GUID] uniqueidentifier default newid()' from sys.tables

Ooops… it generated an error.

Msg 33422, Level 16, State 1, Line 4
The column ‘GUID’ cannot be added to table ‘dbo.DataBank’ as it is a FileTable.
Adding columns to the fixed schema of a FileTable object is not permitted.

The reason behind this error is that I made an alter statement for even the File Table, while you cannot add any column in the File Table.

I, therefore, need to remove the file table from the query result set. So I did a little research and found that if you use the column is_filetable (data type bit) in sys.tables, you can easily identify whether it as a file table  or not . If is_filetable  is 1, then the table is a File Table else not.
So, I changed the query and it worked perfectly. Now you can see that the query will only generate the script for normal table (not file tables).

Select 'Alter Table ' + quotename(schema_name([schema_id])) +'.' + quotename([name])
+ ' ADD [GUID] uniqueidentifier default newid()' from sys.tables where is_filetable =0

Let me know if you know other ways to find file table as well.