Getting SQL Server Cross database Dependencies
how to check table dependency in sql server using query
how to find table dependencies in sql server
how to find column dependencies in sql server
sys dependencies sql server
sql server generate scripts dependency order
sql table dependency hierarchy
finding cross database dependencies sql server
SQL Server Version - 2008 R2
I am working on evaluating a DMS solution, with an objective of taking over maintenance. The original solution has one central database, that has data pertaining to the manufacturer. It also has one database for each dealer, which means there are a lot of cross database dependencies.
- No DB documentation
- No code comments
- Lots of heaps
- No standard object naming conventions
- The central DB has 460+ tables and 900+ SProcs, in addition to other objects
- Each dealer DB has 370+ tables and 2350+ SProcs, in addition to other objects
As a first step, I am recommending a complete clean-up of the DB, for which it is critical to understand object dependencies, including cross database dependencies. I tried using Red Gate's solution, but the output is way too voluminous. All I want is a list of objects in the databases that do not have any dependencies - they neither depend on other objects, nor are there any objects that depend on them.
Here is the script I have used to get a 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 be creating a table - Dependencies - into which I will be inserting this result set from each DB. As a next step, I will also be creating another table - AllObjects- which will contain a list of all objects in the Databases. Here is the script to do this:
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, a list of name from this table, that do not appear in the referenced_entity_name column in the dependencies table should give a list of objects that I am 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 the questions:
- Some object dependencies seem to be missing in the output. What am I missing?
- How do I validate that my findings are correct?
- I mean is there a different way to do this, so I can compare the results and double check?
Thanks in advance,
You can compare your results to the ones that the following script finds. 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;
Finding Cross Database Dependencies, A stored procedure for finding object dependences across multiple databases and servers in Microsoft SQL Server. Getting SQL Server Cross database Dependencies. I am working on evaluating a DMS solution, with an objective of taking over maintenance. The original solution has one central database, that has data pertaining to the manufacturer. It also has one database for each dealer, which means there are a lot of cross database dependencies.
Oh, MS made a good effort at detecting cross-database dependencies with sys.sql_expression_dependencies, but I've seen it miss things before. In your case, I'd find an example of a missing dependency, and start backtracking: have you dropped it from your query some how? If so, fix your query. Does sys.sql_expression_dependencies omit a certain class of dependencies? Under what conditions? Is dynamic SQL to blame? etc.
You should also run
sp_refreshsqlmodule for each object in sys.sql_modules, and then rerun your code. It forces SQL Server to refresh the dependency info (to the best of its ability).
Now, for validation, set up a trace, and listen for event 114, "Audit Schema Object Access Event", plus the starting and completed events for stored procedure and/or RPC calls. Include columns
RequestID (for MARS-enabled connections). Maybe some others too. "Audit Schema Object Access Event" happens anytime an object is accessed, so exercise the app while this trace is running, then collate the data using SPID + RequestId and compare it to your results using sys.sql_expression_dependencies. If anything is in the trace data that doesn't appear in your dependencies data, then you've missed something.
Different Ways to Find SQL Server Object Dependencies, Example 1: Cross-database dependencies With this example we can get results similar to SQL Server Management Studio (SSMS) for the We recently went through a database consolidation project for a legacy application and needed to find object dependencies across multiple databases. While SQL Server provides some fantastic tools in SQL Server Management Studio and DMVs for finding dependencies, they are all unfortunately scoped to a single database.
If you have to deal with linked servers, I adapted @MilicaMedic's answer to work for cross-server dependencies. I also output column names where available in a dependency.
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 join it to your AllObjects table as you described in your answer.
My code requires two external functions: "splitString", and "AddBracketsWhenNecessary". You can simplify the former and completely eliminate the latter, as you desire. But I use them for other things so they make it into my implementation. The code for both is at the bottom.
Here is the main 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
The code for AddBracketsWhenNecessary:
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('[a-z,_]', 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
Any 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 above):
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 some small changes from the real code I use, so if there are any reference errors, please let me know in the comments and I'll edit.
Resolving Cross Database Dependencies in SQL Change , For objects such as views, for example, SQL Server checks that any When the dependencies are circular (or mutual), we can't get around the with cross-database dependencies uses SCA pre-deployment scripts to create This could also be a good exercise to dig into your databases and learn/document different types of dependencies. Example 1: Cross-database dependencies. Our developer inherited an old application and asked for help to identify cross-database dependencies. There were many integration points, but they were not documented anywhere.
Object Dependency in SQL Server, Server object, the referenced entity. An example of this is a view on a table. The view is the referencing entity and the table is the referenced entity. 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
View the Dependencies of a Stored Procedure, This can be achieved with SQL Server Express if need be. stories / tasks so get the dependency done sooner, or if that was not possible, we In a well-designed SQL Server database, or set of linked databases, it is easy to determine these dependencies, and work out the right sequence for doing things. Finding dependencies via SSMS Most of us need to think very little about finding out about dependencies, since SMO allows SSMS to get dependency information for us for any list of database objects, and display it in a tree structure.
How can we manage cross-database dependencies across , Show cross-database dependencies between objects – Learn more on the SQLServerCentral forums. Hello all,. I use SQL Server 2008 R2. WHERE referencing_minor_id = 0 ' --I don't want to retrieve computed columns. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse 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.
- Do you also care about SYSTEM objects?
- But type = 'U' will only give user tables, right? I also need to consider other user created objects.
- Sorry, "and is_ms_shipped = 0" might be more appropriate for the 2nd query. This should exclude system specific objects.
- How do you know that you are missing something? Be explicit.
- @Raj, I know it's been awhile, but do any of the answers below satisfy you? If so, mark one as the answer.
- Be aware that if a database is offline it will still attempt to be accessed and thus will error out
- Good point, I just added a line to the
WHEREfilter to ignore OFFLINE databases ^_^
- great answer - best answer I've seen for getting cross database dependencies
- The link to the original article is now returning a not found
- It's 2018, I tried to run it and I still can't get dependencies on a column level if dbA.object uses dbBBB.tableA.ColumnC. getTableColumnsIds are still tied to native SYS table I think which is unique to each db.