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).
Tag: mssql
Use SERVERPROPERTY() to Get Server Info in SQL Server
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.
Quick Script that Returns All Properties from SERVERPROPERTY() in SQL Server 2017/2019
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.
Use TYPEPROPERTY() to Return Information about a Data Type in SQL Server
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.
Use TYPE_ID() to Get the ID of a Data Type in SQL Server
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.
Use TYPE_NAME() to Get the Name of a Data Type in SQL Server
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.
Use PARSENAME() to Return Part of an Object Name in SQL Server
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.
How SCHEMA_ID() Works in SQL Server
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.
How SCHEMA_NAME() Works in SQL Server
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.
How to Use OBJECT_ID() on Cross-Database Objects in SQL Server
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.