Блог

Версия SQL Server — 2008 R2

Я работаю над оценкой решения DMS с целью взять на себя техническое обслуживание. Оригинальное решение имеет единую центральную базу данных, содержащую данные, относящиеся к производителю. Он также имеет одну базу данных для каждого дилера, что означает множество взаимосвязей между базами данных.

Проблемы:

• Нет документации по БД
• Никаких комментариев к коду
• Много куч
• Нет стандартных соглашений об именовании объектов
• Центральная база данных содержит более 460 таблиц и более 900 SPROCS, в дополнение к другим объектам
• Каждая база данных дилера содержит более 370 таблиц и более 2350 SPROCS, в дополнение к другим объектам

В качестве первого шага я рекомендую полную очистку базы данных, для чего крайне важно понимать зависимости объектов, включая зависимости между базами данных. Я попытался использовать решение Red Gate, но результат получился слишком объемным. Все, что мне нужно, — это список объектов в базах данных, которые не имеют никаких зависимостей — они не зависят ни от других объектов, ни от каких-либо объектов, которые зависят от них.

Вот сценарий, который я использовал для получения списка зависимостей:

 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 
 

Я буду создавать таблицу зависимостей, в которую я буду вставлять этот результирующий набор из каждой базы данных. В качестве следующего шага я также создам другую таблицу — allObjects, которая будет содержать список всех объектов в базах данных. Вот сценарий для этого:

 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'
)
 

Теперь список имен из этой таблицы, которые не отображаются в столбце referenced_entity_name в таблице зависимостей, должен содержать список объектов, которые я ищу.

 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
 

Теперь вопросы:

1. Некоторые зависимости объектов, похоже, отсутствуют в выходных данных. Чего мне не хватает?
2. Как мне проверить правильность моих выводов?
3. Я имею в виду, есть ли другой способ сделать это, чтобы я мог сравнить результаты и перепроверить?

Заранее спасибо,

Raj

Вы можете сравнить свои результаты с теми, которые находит следующий скрипт. Вот полная статья

 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;
 

О, MS приложила немало усилий для обнаружения зависимостей между базами данных с помощью sys.sql_expression_dependencies, но я видел, как раньше это пропускало вещи. В вашем случае я бы нашел пример отсутствующей зависимости и начал откат: вы каким-то образом удалили ее из своего запроса? Если это так, исправьте свой запрос. Пропускает ли sys.sql_expression_dependencies определенный класс зависимостей? При каких условиях? Виноват ли динамический SQL? и т.д.

Вы также должны выполнить sp_refreshsqlmodule для каждого объекта в sys.sql_modules , а затем повторно запустить свой код. Это заставляет SQL Server обновлять информацию о зависимостях (в меру своих возможностей).

Теперь для проверки настройте трассировку и прослушайте событие 114, «Событие доступа к объекту схемы аудита», а также начальные и завершенные события для вызовов хранимых процедур и / или RPC. Включите столбцы DatabaseName , ParentName , ObjectName , ServerName , SPID и RequestID (для подключений с поддержкой MARS). Может быть, и некоторые другие тоже. «Событие доступа к объекту схемы аудита» происходит при каждом обращении к объекту, поэтому запустите приложение во время выполнения этой трассировки, затем сопоставьте данные с помощью SPID RequestID и сравните их с вашими результатами, используя sys.sql_expression_dependencies. Если в данных трассировки есть что-то, чего нет в ваших данных зависимостей, значит, вы что-то пропустили.

Если вам приходится иметь дело со связанными серверами, я адаптировал ответ @MilicaMedic для работы с межсерверными зависимостями. Я также вывожу имена столбцов, если они доступны в зависимости.

Вы можете использовать его следующим образом:

 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'
 

Оттуда вы присоединяете его к своей таблице allObjects, как вы описали в своем ответе.

Для моего кода требуются две внешние функции: «splitString» и «AddBracketsWhenNecessary». Вы можете упростить первое и полностью исключить последнее, по вашему желанию. Но я использую их для других целей, чтобы они были включены в мою реализацию. Код для обоих находится внизу.

Вот основная процедура:

 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
 

Код для ADDBRACKETS при необходимости:

 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
 

Любая, наконец, моя функция разделения (но смотрите Здесь Arnold Fribble, если вам нужна более простая версия, или используйте встроенную функцию, если у вас SQLServer 2016 или выше):

 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
 

Мне пришлось внести некоторые небольшие изменения в реальный код, который я использую, поэтому, если есть какие-либо ошибки в ссылках, пожалуйста, дайте мне знать в комментариях, и я отредактирую.