Getting SQL Server Cross database Dependencies

问题:

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.

The problems:

• 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:

1. Some object dependencies seem to be missing in the output. What am I missing?
2. How do I validate that my findings are correct?
3. I mean is there a different way to do this, so I can compare the results and double check?

Thanks in advance,

Raj

回答1:

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;

回答2:

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 DatabaseName, ParentName, ObjectName, ServerName, SPID and 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.

回答3:

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.