Identify Full Text Catalog Files I'm trying to write a query for a report to show how much free space is in our data files so we can plan storage for the future. I'm pulling data out of sys.database_files, and I can narrow it down to just type 0 (Rows) data - we're not worried about log growth at this point.

The problem I'm having is that a number of databases have a full text catalog. The Full Text Catalog file is also displaying as type 0. We want to exclude these from the report as well. Is there anyway we can tell from sys.database_files (or join back to that) so we can just see the data files?

I notice any newly created Full Text Catalog files in SQL Server 2016 will have type = 4, but that doesn't really help me on 2014 (or upgraded databases in 2016).
I believe type = 4 only applies to pre-2008 fulltext files which have been upgraded, because since then there has been no way to create separate fulltext files -- only separate filegroups. (See type = 4 doc for SQL 2012 at https://msdn.microsoft.com/en-gb/library/ms174397(v=sql.110).aspx which confusingly says the same as for 2016 except version number.)

Something like http://www.connectsql.com/2011/01/sql-server-how-to-get-physical-path-of.html may technically do what you are looking for, but won't actually be relevant for your report.