Two new stored procedures introduced in SQL Server 2019 are sys.xp_copy_file and sys.xp_copy_files, which enable you to copy files.
Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy files without relying on xp_cmdshell.
In SQL Server, you can use the FILEPROPERTY() function to return the property value for a specified database file. The returned value is either 1 or 0 (or NULL if the input is invalid).
To use it, provide the file’s logical file name and the property value that you want returned.
In SQL Server, you can use the FILEGROUPPROPERTY() function to return the filegroup property value for a specified name and filegroup value. The returned value is either 1 or 0 (or NULL if the input is invalid).
To use it, provide the filegroup name and the property value that you want returned.
In SQL Server, you can use the FILEGROUP_ID() function to return the ID of a filegroup, based on its name.
To return the filegroup ID, simply pass its name to the function.
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.
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.
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.
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.
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.
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).