In SQL Server, you can query the sys.filegroups system catalog view to return a list of all filegroups for the current database.
This view contains a row for each data space that is a filegroup. In other words, your results contain one row per filegroup.
This article offers five options for checking if a table exists in SQL Server. Most options involve querying a system view, but one of the options executes a system stored procedure, and another involves a function.
I also include some simple IF statements that can be modified to suit your circumstance.
In SQL Server, you can use the DB_ID() function to return the ID of the current database, or another specified database.
The way it works is, you pass the name of the database as an argument, and then the function will return the ID of that database. If you don’t pass a name it will return the ID of the current database.
In SQL Server, you can use the DB_NAME() function to return the name of the current database, or another specified database.
The way it works is, you pass the ID of the database as an argument, and then the function will return the name of that database. However, if you don’t pass an ID it will return the name of the current database.
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.
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!
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.
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.