Two of the metadata functions available in SQL Server include DB_NAME() and ORIGINAL_DB_NAME(). Both functions are similar in that they return the name of a database. But they are also different. You definitely don’t want to confuse the two, as they serve different purposes.

In a nutshell, each function works as follows:

• DB_NAME() returns the name of a specified database. If you don’t explicitly specify a database, it returns the current database.
• ORIGINAL_DB_NAME() returns the database name specified by the user in the database connection string.

Continue reading →

In SQL Server, you can use the ORIGINAL_DB_NAME() function to return the database name specified by the user in the database connection string.

This function is not to be confused with the DB_NAME() function, which returns either a specific database, or the current one.

When you first make a connection to SQL Server, you have the option of specifying the initial database. For example, when using a command line interface such as sqlcmd, you can use the -d parameter to specify the initial database. If you don’t use this parameter, your login’s default database will be the initial database.

Once you connect, you can switch to a different database, but your original database will always be the same. In other words, ORIGINAL_DB_NAME() will always return the same database throughout your session, even if you switch to a different database.

Continue reading →

Below is a (slightly) improved script that returns all properties from the SERVERPROPERTY() function in SQL Server.

This is similar to the quick script that I posted recently. The difference is that for some properties, the script below uses CASE statements to provide a short explanation of the configured value, rather than the raw value (which is often a “1” or “0”). The aforementioned quick script, on the other hand, simply outputs the raw data for all rows.

Continue reading →

In SQL Server, the SERVERPROPERTY() function returns information about the server instance.

You can use it to return information such as the edition of SQL Server being used, the server name, the machine name, and more. As of SQL Server 2017 and 2019, you can use it to return up to 44 properties.

Continue reading →

The following script returns all properties (and their respective values) from the SERVERPROPERTY() function in SQL Server 2017 and 2019.

The property names are returned in the first column, and their raw values are returned in the second column.

In total, 44 properties are returned by this script.

Continue reading →

In SQL Server, you can use the TYPEPROPERTY() function to return information about a data type.

You provide the name of the data type, as well as the property you want returned, and it returns information about that property for the given data type.

You can use TYPEPROPERTY() for system data types and user-defined data types.

Continue reading →

In SQL Server, you can use the TYPE_ID() function to return the ID of a data type, based on its name. This can be useful when querying a system view that stores a data type’s ID but not its name. It’s usually easier to remember the name. Not so easy to remember the ID.

You can use TYPE_ID() for system data types and user-defined data types.

Continue reading →