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.
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.
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.
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.
You can use the @@DBTS function to get the current rowversion of a given database in SQL Server. More specifically, it returns the last-used rowversion value of the current database.
In SQL Server, rowversion is a data type that exposes automatically generated, unique binary numbers within a database. It allows you to version-stamp table rows with a unique value. This helps maintain the integrity of the database when multiple users are updating rows at the same time.
Each SQL Server database has a a counter that is incremented for each insert or update operation that is performed on a table that contains a column with the rowversion data type (or its timestamp synonym, which is flagged for deprecation).
If a table contains a rowversion (or timestamp) column, any time a row is inserted or updated, the value of the rowversion column is set to the current rowversion value. This is true, even when an UPDATE statement doesn’t result in any changes to the data.
As of SQL Server 2017 (and SQL Server 2019 preview), four data types are flagged for deprecation in a future version of SQL Server.
They are:
Microsoft advises that these data types are currently in maintenance mode and they will be removed in a future version of SQL Server. This means you should avoid using these data types in new development work, and you should plan to modify applications that currently use them.
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:
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 readingOne 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.
In SQL Server, you can use the sys.dm_sql_referenced_entities() system dynamic management function to get a list of all user-defined entities that are referenced by name, in the definition of a given entity.
In other words, it returns a list of all user-defined entities that a specific entity depends on.
Continue reading