Does an existing varbinary(max) use FILESTREAM or not?

Question

How can I check whether a varbinary(max) column is using FILESTREAM ? For argument's sake, lets say that I inherited a DB and I wanted to check which varbinary(max) columns are using FILESTREAM and which are not. I have tried running sp_columns which does not seem to show anything relevant. If I "right-click -> properties" on the column from SQL Server Management Studio, it does not show anything either. Does anybody know how to check for this?

Answer 1

Try this query

SELECT 
    OBJECT_SCHEMA_NAME(C.object_id) AS SchemaName
  , OBJECT_NAME(C.object_id) AS TableName
  , C.name AS ColumnName
FROM 
    sys.columns AS C
WHERE 
    C.is_filestream = 1;