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.
Tag: t-sql
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.
Get an Object’s ID from its Name in SQL Server: OBJECT_ID()
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
.
How to Get an OBJECT_NAME() from a Different Database in SQL Server
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.
Use OBJECT_NAME() to Get an Object’s Name from its object_id in SQL Server
When using SQL Server, if you ever find yourself about to do a join against the sys.objects system catalog view in order to get the name of an object, maybe stop and read this first.
Transact-SQL has a built-in function called OBJECT_NAME() that returns the name of an object, based on its ID.
In other words, if you have the object’s ID (for example, the object_id column), you can simply pass that ID to the OBJECT_NAME() function, and it will return the object’s name for you – no join required!
4 Ways to Get a Stored Procedure’s Definition using Transact-SQL
This article presents 4 ways of using T-SQL to get the definition of a stored procedure in SQL Server.
The definition is the actual T-SQL statement used to create the stored procedure.
Three of the methods here are exactly the same as the ones used for returning the definition of a view (except here, they’re being used on stored procedures instead of views).
4 Ways to Get a View’s Definition using Transact-SQL
This article presents 4 ways of using T-SQL to get the definition of a view in SQL Server.
The view definition is the actual T-SQL statement used to create the view.
Get View Information with the VIEWS Information Schema View in SQL Server
In SQL Server, you can use the Transact-SQL VIEWS system information schema view to return information about one or more views in the current database. It returns one row for views that can be accessed by the current user in the current database.
To use this view, specify the fully qualified name of INFORMATION_SCHEMA.VIEWS.