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.
