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).
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.
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.
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.
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.
In SQL Server, you can use the TYPE_NAME() function to return the name of a data type, based on its ID. This can be useful when querying a system view such as sys.columns that returns the type’s ID but not its name.
You can use TYPE_NAME() for system data types and user-defined data types.
In SQL Server, you can use the PARSENAME() function to return part of an object name.
For example, you can use it to return the schema part (or any other part) of a four part name such as server.schema.db.object.
In SQL Server, you can use the SCHEMA_ID() function to return the ID of a given schema. More specifically, this function returns the schema ID associated with a schema name.
It’s like SCHEMA_NAME() except it returns the schema’s ID instead of the name (and it accepts the name parameter instead of ID).
If you don’t pass a schema name to the function, it returns the ID of the default schema of the caller.
In SQL Server, you can use the SCHEMA_NAME() function to return the name of a particular schema. The way it works is that it returns the schema name associated with a schema ID.
If you don’t pass a schema ID to the function, it returns the name of the default schema of the caller.