How to distinguish keys in created filetable

Question

I follow an online example of how to create a file table with script like this

  CREATE TABLE [dbo].[Users] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FGFS]
    GO

And when I run the query like

SELECT * FROM sys.key_constraints WHERE parent_object_id=xxxxx
SELECT * FROM sys.index_columns WHERE object_id=yyyyy

The first one gives me 3 keys (1PK+2UQs)

How can I learn which one is either FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME or FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME or FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME?

Answer 1

Like any table, you can quickly get the index details with sp_help:

EXEC sp_help N'dbo.Users';

The index details will be like:

+-----------------------------+------------------------------------------------------+---------------------------+
|         index_name          |                  index_description                   |        index_keys         |
+-----------------------------+------------------------------------------------------+---------------------------+
| PK__Users__5A5B77D52FBBA93E | nonclustered, unique, primary key located on PRIMARY | path_locator              |
| UQ__Users__9DD95BAF3C1BB377 | nonclustered, unique, unique key located on PRIMARY  | stream_id                 |
| UQ__Users__A236CBB35BE6FD63 | nonclustered, unique, unique key located on PRIMARY  | parent_path_locator, name |
+-----------------------------+------------------------------------------------------+---------------------------+

The query below will return the constraint name and column name(s) using the catalog views:

SELECT kc.name AS constraint_name, c.name AS column_name
FROM sys.filetables AS t
JOIN sys.key_constraints AS kc ON kc.parent_object_id = t.object_id
JOIN sys.index_columns AS ic ON ic.object_id = kc.parent_object_id AND ic.index_id = kc.unique_index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE t.object_id = OBJECT_ID(N'dbo.Users', 'U')
ORDER BY kc.name, ic.key_ordinal;