In SQL Server, you can use the FILEGROUP_NAME() function to return the name of a filegroup, based on its ID.
To return the filegroup name, simply pass its ID to the function.
Return All Filegroups for the Current Database in SQL Server
In SQL Server, you can query the sys.filegroups system catalog view to return a list of all filegroups for the current database.
This view contains a row for each data space that is a filegroup. In other words, your results contain one row per filegroup.
FILE_ID() vs FILE_IDEX() in SQL Server: What’s the Difference?
In SQL Server, you can use the FILE_IDEX() function to return the ID of a given database file. You can also use the FILE_ID() function to do the same thing. Both functions serve pretty much the same purpose, so why does T-SQL have both functions?
It appears that FILE_IDEX() is a replacement for FILE_ID(), and it supports a larger range of file IDs. Microsoft now recommends against using FILE_ID(), as it’s in maintenance mode and may be removed in a future version of SQL Server.
So if you’re looking for a quick answer for which function to use, use FILE_IDEX().
But if you’re interested in the difference between these two functions, read on.
Use FILE_IDEX() to Return the ID of a Database File in SQL Server
In SQL Server, you can use the FILE_IDEX() function to return the ID of a given database file.
To do this, pass the logical file name of the database file to the function. This is the name that corresponds to the name column in the sys.master_files catalog view or the sys.database_files catalog view. Those views also contain the file ID, but FILE_NAME() saves you from having to query those views.
Use FILE_NAME() to Return the Logical File Name for a Given File ID in SQL Server
In SQL Server, you can use the FILE_NAME() function to return the logical file name of a given database file.
To do this, pass the file ID to the function. This is the ID that corresponds to the file_id column in the sys.master_files catalog view or the sys.database_files catalog view. Those views also contain the logical file name, but the FILE_NAME() function saves you from having to query those views.
Check Whether a Table Has a TIMESTAMP Column in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has a timestamp column.
To do this, pass the table’s object ID as the first argument, and TableHasTimestamp as the second argument. The function returns a 1 or a 0 depending on whether or not it has a timestamp column.
A return value of 1 means that the table does have a timestamp column, and a value of 0 means that doesn’t.
This also works for columns that have been defined as rowversion (timestamp is the deprecated synonym for rowversion).
List All Foreign Keys on a Table in SQL Server
Below are two methods you can use to return a list of foreign keys for a given table in SQL Server.
This is similar to returning the foreign keys based on the referenced/primary key table, except here, I’m returning the foreign keys based on the referencing/foreign key table itself.
Check Whether a Table Has a Foreign Key in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has one or more foreign key constraints.
To do this, pass the table’s object ID as the first argument, and TableHasForeignKey as the second argument. The function returns a 1 or a 0 depending on whether or not it has a foreign key constraint.
A return value of 1 means that the table does have a foreign key constraint, and a value of 0 means that doesn’t. A return value of 1 applies regardless of how many foreign keys the table has (as long as it has at least one).
If you want an actual list of the foreign keys, see List All Foreign Keys on a Table in SQL Server.
Return All Foreign Keys that Reference a Given Table in SQL Server
If you need to return all foreign keys that reference a given table in SQL Server, try one of the following methods.
The first method queries the sys.foreign_keys system view. The second method executes the sp_fkeys system stored procedure.
Check if a Table is Referenced by a Foreign Key in SQL Server with OBJECTPROPERTY()
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table is referenced by a foreign key.
To do this, pass the table’s object ID as the first argument, and TableHasForeignRef as the second argument. The function returns a 1 or a 0 depending on whether or not it is referenced by a foreign key.
A return value of 1 means that the table is referenced by a foreign key, and a value of 0 means that it’s not.
Note that the examples presented here don’t list out the foreign keys or their tables or anything like that. They simply return a true/false value that you can use to test whether or not a table is referenced by a foreign key. If you need to list out all foreign keys that reference a given table, see Return All Foreign Keys that Reference a Given Table in SQL Server. The examples in that article list out each foreign key, as well as the foreign key table/s, and the primary key table.