Database Object Depencencies

Sometimes you want to find the database object dependency within a database or instance, and luckily this is something Microsoft has thought of in the form of a view called sys.sql_expression_dependencies

Microsoft also tells us a fair bit about this magnificent view:

“(This view) Contains one row for each by-name dependency on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity.”

Microsoft Docs

I wanted to find all database objects that has a dependency to other databases, to make sure that decommissioning one database did not affect a bunch of views in a database we wanted to keep operational.

The view mentioned is a database wide view, so if you need to collect data from a whole instance there are a few ways to automate this, but I have a suggestion using a separate table, and the undocumented procedure sp_MSforeachdb. I am not going to say much about this procedure, other than this is used on your own risk. Microsoft can change or remove this procedure at any point, so please do not use it as part of an important production job or similar.

First you create the query which provide you with the data you think you need. In my case it looked something like this:

DB_ID() AS [DBid],
OBJECT_SCHEMA_NAME(referencing_id) AS [SchemaName], 
OBJECT_NAME(ssed.referencing_id) AS [ObjectName], ssed.referenced_database_name, 
FROM sys.sql_expression_dependencies ssed
WHERE referenced_database_name IS NOT NULL;

This query will provide you with a list like the image below, depending on your dependencies. Note that if you have no depencendies outside the database in question, no rows are returned.

The data here is from a Reporting Services databases which refer to the ReportServerTempDB. Probably not surprising, but goot to know.

I have also fetched some information from the fantastic INFORMATION_SCHEMA. This time I have only collected the definition of the view, however if you are going to export this list into a spreadsheet to share with someone, you might want to comment this out. In my experience this is not a column Excel handles well.

Next part is to make sure you have a table that will take all the data provided by the query created. I created the following table in my DBA_data. We can discuss whether it should have some index or not, but lets look at that another time.

CREATE TABLE dbo.referencesforobjects(
ReferencingDatabaseName nvarchar(256) not null,
ReferencingDatabaseId int,
ReferencingSchemaName nvarchar(256),
ReferencingObjectName nvarchar(256),
referenced_database_name sysname,
referenced_schema_name sysname,
referenced_entity_name sysname,
ViewDefinition nvarchar(max)

When the table is created, I am basically ready to run the MS-procedure to execute the select statement (as an insert) to gather the data efficiently.

exec sp_MSforeachdb @command1='USE ?; 
INSERT [DBA_Data].[dbo].[referencesforobjects] 
SELECT DB_NAME() AS [DBName], DB_ID() AS [DatabaseID], OBJECT_SCHEMA_NAME(referencing_id) AS [SchemaName], OBJECT_NAME (ssed.referencing_id) AS [ObjectName], ssed.referenced_database_name, ssed.referenced_schema_name, ssed.referenced_entity_name, isv.VIEW_DEFINITION
FROM sys.sql_expression_dependencies ssed
WHERE referenced_database_name IS NOT NULL;'

The query is now executed in every database and the result set is inserted to the dba_data.dbo.referencesforobjects table. You can query it to find the result for a single or multiple database, or even all databases except system dbs.

SELECT [ReferencingDatabaseName]
      ,[ViewDefinition] --Skip column if pasting into Excel
FROM [DBA_Data].[dbo].[referencesforobjects]
WHERE [ReferencingDatabaseId] > 4
ORDER BY [ReferencingDatabaseName]

This is one way to collect information regarding views and other database object that has references to objects in a different database. Good to know how the information in your databases crisscross before the databases are dropped… Also a good way to find views and procedures that no longer works, as they reference databases that do no longer exist.

Let me know if you have any comments or suggestions, or if there is an easier way to gather this information in large instances.

Useful DBA tools

This is probably one of the posts that never will be completed, as the tools I use change over time. However at the moment this is an overview of the tools I use and how to find them.

DBA ToolsCommunity driven SQL Server PowerShell module, which continously gets more functions and features.
Ola Hallengren
Maintenance Solution
The maintenance tool to help you automate the maintenance you absolutely need!
sp_WhoIsActiveA comprehensive activity monitoring sp, which is a helpful troubleshooting partner.
Created by Adam Machanic
sp_BlitzDiscover your database servers possibilities for improvement.
Created by Brent Ozar
SQL Server
Diagnostic Queries
Magnificent DMV Queries which will provide you with information you did not even thought you wanted.
Created by Glenn Berry
DBAChecksYou probably need to validate your environment, and this is a good tool for it.
SQL Server
Management Studio
Perhaps a given, but it is the main tool used by most DBAs I would recon.
Azure Data StudioAzure Data Studio (previously SQL Server Operations Studio), a tool focusing on getting an overview and documenting stuff.
Microsoft Update CatalogGood to have should you need to patch servers offline.
Execution Plan ReferenceSQL Server Execution Plan Reference
Created by Hugo Kornelis
Wait TypesSQL Server Wait Types Library
Created by SQLSkills
Latch ClassesSQL Server Latch Classes Library
Created by SQLSkills

WMI provider says no.

Lets start with a simple issue that I have struggled with a few times. I try to start SQL Server Configuration Manager and is met with the following puzzling message:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

The Internet list many culprits and most agree that WMI is not to blame this time. Exactly what causes the error seems to be somewhat random. Someone blames 32 vs. 64-bit mixed installations. I have experienced it after patching or installing multiple different instances on the same server, or even just patching an instance. Lets all agree it is caused by change of some kind.

Usually this issue is rather easy to resolve. Open CMD as administrator and type (or copy) the following and take care to replace 000 with your SQL Server version number (check the table below if you are uncertain). If you also installed SQL Server to some other location than the “Program Files” folder you will have to correct that in the statement below.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\000\Shared\sqlmgmproviderxpsp2up.mof"
Version NameVersion Number
2008 R2105

Microsoft provides a bit of information regarding the issue at Microsoft Docs.