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.
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!
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).
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.
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.
In SQL Server, you can use the Transact-SQL PARAMETERS system information schema view to find the parameters used in a stored procedure or user-defined function.
More specifically, it returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database.
To use this view, specify the fully qualified name of INFORMATION_SCHEMA.PARAMETERS.