Tag: functions

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 →

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 →

Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance (T-SQL Example)

Posted on by Ian

In SQL Server, the sys.dm_os_performance_counters system dynamic management view returns the performance counters maintained by the server.

One of the many things you can do with sys.dm_os_performance_counters is return a list of deprecated features in the current instance of SQL Server. You can also use this list to see how many times a deprecated feature has been used since SQL Server was started.

This is quite possibly the quickest way to find out if you’re using a deprecated feature in SQL Server.

The view returns a row per performance counter maintained by the server. The SQLServer:Deprecated Features object provides a counter to monitor the features designated as deprecated. It has a cntr_value column, which provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started.

Therefore, by running a query against this view, we can return all deprecated features along with a count of how many times each one was encountered since SQL Server last started.

Continue reading →

How to List the Deprecated Features in a SQL Server Instance using T-SQL

Posted on by Ian

In SQL Server, you can use the sys.dm_os_performance_counters system dynamic management view to return a list of the features designated as deprecated in the current instance of SQL Server.

To return just the deprecated features, filter the view to just the SQLServer:Deprecated Features object.

The view also includes a counter that lists the number of times each deprecated feature was encountered since SQL Server last started. This can be helpful for identifying whether or not your application is still using any deprecated features.

Running this on SQL Server requires VIEW SERVER STATE permission.

Continue reading →

Find Dependencies in SQL Server: sql_expression_dependencies

Posted on by Ian

In SQL Server, you can use the sys.sql_expression_dependencies system catalog view to return all dependencies on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules.

You can use this view to:

  • Return entities that depend on a given entity
  • Return entities on which a given entity depends

So for example, you could use it to return all objects that reference a specific table. You could also use it to return all objects that a specific stored procedure references within its code.

Continue reading →

Example of SQL Server’s sys.dm_sql_referenced_entities() Returning an Entity that References a Linked Server

Posted on by Ian

One of the things about the sys.dm_sql_referenced_entities() system dynamic management function is that you can use it on cross-database and cross-server entities.

This means you can find referenced entities that are in a different database and even on a different server.

This article provides an example of sys.dm_sql_referenced_entities() returning a stored procedure that queries a database on a linked server.

Continue reading →