Category: DBMS

Database Management Systems

Use OBJECT_NAME() to Get an Object’s Name from its object_id in SQL Server

Posted on by Ian

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!

Continue reading →

4 Ways to Get a Stored Procedure’s Definition using Transact-SQL

Posted on by Ian

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).

Continue reading →

Get View Information with the VIEWS Information Schema View in SQL Server

Posted on by Ian

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.

Continue reading →

Return the Parameters of a Stored Procedure or User-Defined Function in SQL Server (T-SQL Examples)

Posted on by Ian

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.

Continue reading →

Find the Columns Returned by a Table-Valued Function (T-SQL Examples)

Posted on by Ian

In SQL Server, you can use the Transact-SQL ROUTINE_COLUMNS system information schema view to find the columns returned by a table-valued function.

More specifically, it returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.

To use this view, specify the fully qualified name of INFORMATION_SCHEMA.ROUTINE_COLUMNS.

Continue reading →

Return Stored Procedures & Functions in a SQL Server Database: ROUTINES (T-SQL Examples)

Posted on by Ian

In SQL Server, you can use the Transact-SQL ROUTINES system information schema view to return a list of stored procedures and functions in the current database.

More specifically, it returns a list of all stored procedures and functions that can be accessed by the current user in the current database.

You can also use ROUTINES simply to return information about a specific procedure or function if required.

To use this view, use the fully qualified name of INFORMATION_SCHEMA.ROUTINES.

Continue reading →

Don’t Use sp_depends in SQL Server (it’s Deprecated)

Posted on by Ian

SQL Server has a system stored procedure called sp_depends that returns information about dependencies between objects within the current database.

Microsoft has marked this stored procedure as deprecated, which means that it’s in maintenance mode and may be removed in a future version of SQL Server. You should avoid using sp_depends in new development work, and you should modify applications that currently use it to use either sys.dm_sql_referencing_entities() or sys.dm_sql_referenced_entities() instead (depending on whether you need referencing entities, or referenced entities to be returned.

Continue reading →

Don’t Use sys.sql_dependencies in SQL Server (it’s Deprecated)

Posted on by Ian

SQL Server has a system catalog view called sys.sql_dependencies that returns information about dependencies between entities.

Microsoft has marked this view as deprecated, which means that it’s in maintenance mode and may be removed in a future version of SQL Server. You should avoid using sys.sql_dependencies in new development work, and you should modify applications that currently use it to use sys.sql_expression_dependencies instead.

Continue reading →

Using Extended Events to Log Deprecated Features Being Used in a SQL Server Instance (T-SQL Example)

Posted on by Ian

Extended events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.

This article demonstrates how extended events can be used to create a log file that contains all deprecated features that are still being used in an instance of SQL Server. The log records all occurrences since the event session was started.

If you just want a quick count of how many times a deprecated feature has been used since SQL Server was started up, see Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance.

But if you need a more detailed log that includes stuff like; the SQL statement used that contains the deprecated feature, the database it was run against, the user that ran it, the time it was run, etc, read on.

Continue reading →