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.
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.
In SQL Server, you can use the OBJECT_ID() function to return an object’s ID, based on its name.
This can be useful when you need an object’s ID, but you only know its name.
The official definition of OBJECT_ID() is that it returns the database object identification number of a schema-scoped object
.
If you ever need to use the OBJECT_NAME() function to get an object’s name from a different database in SQL Server, you might run into issues if you don’t know how it works.
You probably know that OBJECT_NAME() accepts an object_id argument that tells SQL Server which object to get the name from.
What you may or may not know, is that this function also accepts an optional database_id argument that tells SQL Server which database the object_id belongs to.
By default, SQL Server assumes that object_id is in the context of the current database. In this case, a query that references an object_id in another database will return NULL or (even worse) incorrect results.