In SQL Server, the DATABASEPROPERTYEX() function returns the current setting of the specified database option or property for the specified database.
For example, you can use it to return the collation of a database, check the level of user access, etc.
It accepts two arguments: the database, and the property you want information about.
In SQL Server, you might’ve encountered the OBJECTPROPERTY() function, only to find out that there’s also a OBJECTPROPERTYEX() function that appears to do exactly the same thing.
What’s going on here? Why the need for two functions that do the same thing?
My understanding is that Microsoft chose to add OBJECTPROPERTYEX() to extend the functionality of OBJECTPROPERTY(), rather than to introduce changes to OBJECTPROPERTY() that would potentially break existing code on legacy systems.
So there are some differences between the two functions.
In SQL Server, the OBJECTPROPERTYEX() function returns information about schema-scoped objects in the current database.
This function does exactly the same thing as OBJECTPROPERTY(), except that it supports more properties, and the return value is different. The OBJECTPROPERTYEX() function returns a sql_variant type, whereas OBJECTPROPERTY() returns an int type.
In SQL Server, the OBJECTPROPERTY() function returns information about schema-scoped objects in the current database.
These schema-scoped objects are the ones you can see by querying the sys.objects system catalog view. It can’t be used for objects that are not schema-scoped.
You can use OBJECTPROPERTY() to check if an object is a table, view, stored procedure, etc. You can also use it to check if a table has a primary key, foreign key, foreign key reference, etc.
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.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.
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.
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.
If you need to run the SERVERPROPERTY() function against a linked server in SQL Server, you can use a pass-through query. To do this, pass the SERVERPROPERTY() function as the second argument to the OPENQUERY() function (the first argument is the linked server name).