Getting SQL Server Cross Database Dependencies

Question

SQL Server Version 2008 R2

I am working on evaluating a DMS solution in order to receive service. The original solution has one central database with data related to the manufacturer. It also has one database for each dealer, which means that there are many dependencies between the databases.

Problems:

• Documentation without DB
• No comment comments
• A lot of heaps
• No standard object naming conventions
• The central database has 460+ tables and 900+ SProcs, in addition to other objects
• Each dealer database has 370+ tables and 2350+ SProcs, in addition to other objects

As a first step, I recommend a thorough database cleanup, for which it is important to understand the dependencies of objects, including cross-dependent databases. I tried using the Red Gate solution, but the output is too voluminous. All I want is a list of objects in databases that do not have any dependencies - they are not dependent on other objects and are not dependent on any objects.

Here is the script I used to get the list of dependencies:

SELECT DB_NAME() referencing_database_name, OBJECT_NAME (referencing_id) referencing_entity_name, ISNULL(referenced_schema_name,'dbo') referenced_schema_name, referenced_entity_name, ao.type_desc referenced_entity_type, ISNULL(referenced_database_name,DB_NAME()) referenced_database_name FROM sys.sql_expression_dependencies sed JOIN sys.all_objects ao ON sed.referenced_entity_name = ao.name 

I will create a table - Dependencies - in which I will insert this result set from each database. As a next step, I will also create another table - AllObjects - which will contain a list of all the objects in the Databases. To do this, run the script:

 SELECT DB_NAME() DBName, name, type_desc FROM sys.all_objects WHERE type_desc IN ( 'VIEW', 'SQL_TABLE_VALUED_FUNCTION', 'SQL_STORED_PROCEDURE', 'SQL_INLINE_TABLE_VALUED_FUNCTION', 'USER_TABLE', 'SQL_SCALAR_FUNCTION' ) 

Now the list of names from this table that do not appear in the referenced_entity_name column in the dependency table should contain a list of the objects I'm looking for.

 SELECT AO.DBName, AO.name, AO.type_desc FROM AllObjects AO LEFT OUTER JOIN Dependencies D ON D.referenced_database_name = AO.DBName AND D.referenced_entity_name = AO.name AND D.referenced_entity_type = AO.type_desc WHERE D.referenced_database_name IS NULL AND D.referenced_entity_name IS NULL AND D.referenced_entity_type IS NULL 

Now questions:

• The output is missing some object dependencies. What am I missing?
• How to confirm that my conclusions are correct?
• I mean, is there any other way to do this, so I can compare the results and double check?

Thanks in advance,

Rajah

Answer 1

You can compare your results with those found in the following script. Here is the full article

 CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS SET NOCOUNT ON; CREATE TABLE #databases( database_id int, database_name sysname ); INSERT INTO #databases(database_id, database_name) SELECT database_id, [name] FROM sys.databases WHERE 1 = 1 AND [state] <> 6 /* ignore offline DBs */ AND database_id > 4; /* ignore system DBs */ DECLARE @database_id int, @database_name sysname, @sql varchar(max); CREATE TABLE #dependencies( referencing_database varchar(max), referencing_schema varchar(max), referencing_object_name varchar(max), referenced_server varchar(max), referenced_database varchar(max), referenced_schema varchar(max), referenced_object_name varchar(max) ); WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN SELECT TOP 1 @database_id = database_id, @database_name = database_name FROM #databases; SET @sql = 'INSERT INTO #dependencies select DB_NAME(' + convert(varchar,@database_id) + '), OBJECT_SCHEMA_NAME(referencing_id,' + convert(varchar,@database_id) +'), OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), referenced_server_name, ISNULL(referenced_database_name, db_name(' + convert(varchar,@database_id) + ')), referenced_schema_name, referenced_entity_name FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies'; EXEC(@sql); DELETE FROM #databases WHERE database_id = @database_id; END; SET NOCOUNT OFF; SELECT * FROM #dependencies; 

Answer 2

Oh, MS put a lot of effort into discovering dependencies between databases with sys.sql_expression_dependencies, but I saw how it was missing before. In your case, I would find an example of a missing dependency and start backtracking: did you drop it from your request somehow? If so, correct your request. Does sys.sql_expression_dependencies depend on a particular dependency class? Under what conditions? Is dynamic SQL the culprit? and etc.

You must also run sp_refreshsqlmodule for each object in sys.sql_modules, and then re-run your code. This forces SQL Server to update dependency information (as much as possible).

Now, to verify, configure the trace and listen to event 114, “Access event to the audit scheme object,” as well as the start and end events for stored procedures and / or RPC calls. Include columns DatabaseName , ParentName , ObjectName , ServerName , SPID and RequestID (for connections with MARS support). Perhaps some others too. An “audit object access event” event occurs any time an object is accessed, so use the application while this trace is running, then match the data using SPID + RequestId and compare it with the results using sys.sql_expression_dependencies. If there is no data in the trace data that does not appear in your dependency data, then you missed something.

Answer 3

If you have to deal with linked servers, I adapted @MilicaMedic's answer to work with cross-server dependencies. I also output column names where they are available depending on.

You can use it like this:

 create table #dependencies ( referencing_server nvarchar(128), referencing_database nvarchar(128), referencing_schema nvarchar(128), referencing_object_name nvarchar(128), referencing_column nvarchar(128), referenced_server nvarchar(128), referenced_database nvarchar(128), referenced_schema nvarchar(128), referenced_object_name nvarchar(128), referenced_column nvarchar(128) ); insert @dependencies exec crossServerDependencies 'ThisServerName, LinkedServerName, LinkedServerName2, etc' 

From there, you attach it to your AllObjects table, as you described in your answer.

My code requires two external functions: "splitString" and "AddBracketsWhenNeeded". You can simplify the first and completely eliminate the second as you wish. But I use them for other things so that they go into my implementation. The code for both is at the bottom.

Here is the basic procedure:

 create procedure crossServerDependencies @server_names_csv nvarchar(500) = null -- csv list of server names you want to pull dependencies for as -- Create output table if object_id('tempdb..#dependencies') is not null drop table #dependencies; create table #dependencies ( referencing_server nvarchar(128), referencing_database nvarchar(128), referencing_schema nvarchar(128), referencing_object_name nvarchar(128), referencing_column nvarchar(128), referenced_server nvarchar(128), referenced_database nvarchar(128), referenced_schema nvarchar(128), referenced_object_name nvarchar(128), referenced_column nvarchar(128) ); -- Split server csv into table set @server_names_csv = isnull(@server_names_csv, @@servername); declare @server_names table ( server_row int, server_name nvarchar(128), actuallyExists bit ); insert @server_names select server_row = id, server_name, actuallyExists = case when sv.name is not null then 1 else 0 end from dbo.splitString(@server_names_csv, ',') sp cross apply (select server_name = dbo.AddBracketsWhenNecessary(val)) ap left join sys.servers sv on sp.val = dbo.AddBracketsWhenNecessary(sv.name); -- Loop servers declare @server_row int = 0, @server_name nvarchar(50), @server_exists bit = 0, @server_is_local bit = 0, @server_had_some_inserts bit = 0; while @server_row <= (select max(server_row) from @server_names) begin -- Server loop initializations set @server_row += 1; set @server_had_some_inserts = 0; select @server_name = server_name, @server_exists = actuallyExists from @server_names where server_row = @server_row; set @server_is_local = case when @server_name = dbo.AddBracketsWhenNecessary(@@servername) then 1 else 0 end; -- Handle non-existent server (and prevent sql injection) if @server_exists = 0 begin print '"' + @server_name + '" does not exist. ' + 'Please check your spelling and/or access to view the linked server ' + '(running under ' + user_name() + ').'; continue; end -- Get database list if object_id('tempdb..#databases') is not null drop table #databases; create table #databases ( rownum int identity(1,1), database_id int, database_name nvarchar(128) ); declare @sql nvarchar(max) = ' select database_id, [name] from master.sys.databases where state <> 6 -- ignore offline dbs and database_id > 4 -- ignore system dbs and has_dbaccess([name]) = 1 and [name] not in (''ReportServer'', ''ReportServerTempDB'') '; if @server_is_local = 0 begin set @sql = replace(@sql, '''', ''''''); set @sql = 'select * from openquery( @server_name, ''' + @sql + ''')'; end set @sql = 'insert #databases (database_id, database_name)' + @sql; set @sql = replace(@sql, '@server_name', @server_name); exec (@sql); delete #databases where database_name = 'ReportServer'; -- Loop databases declare @rowNum int = 0; while @rowNum <= (select max(rownum) from #databases) begin -- Database loop initializations set @rowNum += 1; declare @database_id nvarchar(max), @database_name nvarchar(max); select @database_id = database_id, @database_name = dbo.AddBracketsWhenNecessary(database_name) from #databases where rownum = @rowNum; -- Get object dependency info set @sql = ' with getTableColumnIds as ( select table_id = o.object_id, table_name = o.name, column_id = c.column_id, column_name = c.name from @database_name.sys.objects o join @database_name.sys.all_columns c on o.object_id = c.object_id ) @insertStatement select ''@server_name'', db_name(@database_id), object_schema_name(referencing_id, @database_id), object_name(referencing_id, @database_id), referencing_column = ringTCs.column_name, isnull(referenced_server_name, ''@server_name''), isnull(referenced_database_name, db_name(@database_id)), isnull(referenced_schema_name, ''dbo''), referenced_entity_name, referenced_column = redTCs.column_name from @database_name.sys.sql_expression_dependencies d left join getTableColumnIds ringTCs on d.referencing_id = ringTCs.table_id and d.referencing_minor_id = ringTCs.column_id left join getTableColumnIds redTCs on d.referenced_id = redTCs.table_id and d.referenced_minor_id = redTCs.column_id '; set @sql = replace(@sql, '@database_id', @database_id); set @sql = replace(@sql, '@database_name', @database_name); if @server_is_local = 0 begin set @sql = replace(@sql, '''', ''''''); set @sql = replace(@sql, '@insertStatement', ''); set @sql = 'select * from openquery(@server_name, ''' + @sql + ''')'; end set @sql = replace(@sql, '@insertStatement', 'insert #dependencies '); set @sql = replace(@sql, '@server_name', @server_name); exec (@sql); -- Database loop terminations if @@rowcount > 0 set @server_had_some_inserts = 1; end -- database loop -- server loop terminations if @server_had_some_inserts = 0 begin declare @remote_user_name nvarchar(255); select @remote_user_name = remote_name from sys.linked_logins li join sys.servers s on li.server_id = s.server_id where remote_name is not null and s.name = 'sisag' print ( 'No dependencies found for ' + @server_name + '. ' + 'If this is unexpected, you may need to run "grant view any definition to ' + '[' + isnull(@remote_user_name, '?') + ']" ' + 'on the remote server.' ); end end -- server loop -- Terminate select * from #dependencies 

Code for AddBracketsWeNeeded:

 create function AddBracketsWhenNecessary ( @objectName nvarchar(250) ) returns nvarchar(250) as begin if left(@objectName, 1) = '[' and right(@objectName, 1) = ']' return @objectName; declare @hasInvalidCharacter bit; select @hasInvalidCharacter = max(isInvalid) from dbo.splitString(@objectName, null) chars cross apply (select isLetter = patindex('[az,_]', val), isNumber = PATINDEX('[0-9]', val) ) getCharType cross apply (select isInvalid = case when isLetter = 1 then 0 when isNumber = 1 and not chars.id = 1 then 0 else 1 end ) getValidity return case when @hasInvalidCharacter = 1 then '[' else '' end + @objectName + case when @hasInvalidCharacter = 1 then ']' else '' end; end 

Finally, my splitter function (but see Arnold Fribble here if you want a simpler version, or use the built-in function if you have SqlServer 2016 or higher):

 create function splitString ( @stringToSplit nvarchar(max), @delimiter nvarchar(50) ) returns table as return with split_by_delimiter as ( select id = 1, start = 1, stop = convert(int, charindex(@delimiter, @stringToSplit) ) union all select id = id + 1, start = newStart, stop = convert(int, charindex(@delimiter, @stringToSplit, newStart) ) from split_by_delimiter cross apply (select newStart = stop + len(@delimiter)) ap where Stop > 0 ), split_into_characters as ( select id = 1, chr = left(@stringToSplit,1) union all select id = id + 1, chr = substring(@stringToSplit, ID + 1, 1) from split_into_characters where id < len(@stringToSplit) ) select id, val = ltrim(rtrim(substring( @stringToSplit, start, case when stop > 0 then stop - start else len(@stringtosplit) end ))) from split_by_delimiter where len(@delimiter) > 0 union all select id, val = chr from split_into_characters where @delimiter = '' or @delimiter is null 

I had to make small changes to the real code that I use, so if there are any link errors, please let me know in the comments and I will edit.