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.
This article offers five options for checking if a table exists in SQL Server. Most options involve querying a system view, but one of the options executes a system stored procedure, and another involves a function.
I also include some simple IF statements that can be modified to suit your circumstance.
In SQL Server, you can use the DB_ID() function to return the ID of the current database, or another specified database.
The way it works is, you pass the name of the database as an argument, and then the function will return the ID of that database. If you don’t pass a name it will return the ID of the current database.
In SQL Server, you can use the DB_NAME() function to return the name of the current database, or another specified database.
The way it works is, you pass the ID of the database as an argument, and then the function will return the name of that database. However, if you don’t pass an ID it will return the name of the current database.
One difference between OBJECT_ID() and OBJECT_NAME() in SQL Server is the syntax used for cross-database queries. By this I mean, when they’re used on objects in a different database.
The OBJECT_NAME() function has an optional argument that you can provide, which specifies the database ID of the database that contains the object you’re trying to get the name of. Providing this argument enables you to get the name of an object on a different database.
The OBJECT_ID() function on the other hand, doesn’t require such an argument. Instead, this function allows you to use a 3-part name to specify the database, schema, and name of the object that you’re trying to get the ID of.
This article contains examples of using OBJECT_ID() to get the name of an object from a different database.