Когда-либо ли вы оказывались в ситуации, когда вам нужно было выполнить SQL-команду в каждой базе данных на вашем экземпляре SQL Server? Установка курсора и итерация через таблицу sysdatabases в базе данных master может быть утомительной задачей. Однако в SQL Server есть удобная недокументированная хранимая процедура, которая может существенно упростить этот процесс.
Хранимая процедура sp_MSforeachdb
Процедура sp_MSforeachdb – это недокументированная хранимая процедура, которая позволяет выполнять одну и ту же команду во всех базах данных на вашем экземпляре SQL Server. Она предоставляет удобный способ выбора данных, обновления данных и даже создания объектов базы данных в нескольких базах данных.
Общий синтаксис
Общий синтаксис использования sp_MSforeachdb выглядит следующим образом:
EXEC sp_MSforeachdb @commandЗдесь @command – это строка переменной длины, которая содержит SQL-команду, которую вы хотите выполнить.
Использование заполнителя “?”
Помимо прямой команды, вы также можете использовать заполнитель “?” в строке @command. Этот заполнитель заменяет имя базы данных и позволяет изменить контекст, в котором выполняется команда. Давайте рассмотрим несколько примеров, чтобы увидеть, как это работает.
Пример 1: Запрос информации из всех баз данных
Предположим, вы хотите запросить системную таблицу из всех баз данных, включая системные базы данных. Вы можете использовать следующий код:
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'Этот запрос вернет список всех таблиц во всех базах данных на вашем экземпляре SQL Server.
Пример 2: Выполнение запроса DDL в пользовательских базах данных
Если вы хотите выполнить запрос DDL во всех пользовательских базах данных, вы можете использовать следующий код:
EXEC sp_MSforeachdb 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE spNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'Этот код создает хранимую процедуру с именем spNewProcedure1 в каждой пользовательской базе данных, исключая системные базы данных. Хранимая процедура возвращает список всех пользователей в базе данных, отсортированный по дате их изменения.
Пример 3: Запрос информации о файлах из всех баз данных
Предположим, вы хотите запросить информацию о файлах из всех баз данных на вашем экземпляре SQL Server. Вы можете использовать следующий код:
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'Этот запрос вернет список всех файлов во всех базах данных на вашем экземпляре SQL Server.
Преимущества использования sp_MSforeachdb
Использование sp_MSforeachdb может сэкономить вам много времени и усилий по сравнению с установкой курсора для итерации по базам данных. Она позволяет выполнять SQL-команды в нескольких базах данных всего одной строкой кода. Кроме того, она обеспечивает гибкость, позволяя изменить контекст, в котором выполняется команда, с помощью заполнителя “?”.
Вывод
Выполнение SQL-команд в нескольких базах данных в SQL Server может быть сложной задачей. Однако хранимая процедура sp_MSforeachdb предоставляет удобное и эффективное решение. Используя эту недокументированную процедуру, вы легко можете запрашивать данные, обновлять данные и создавать объекты базы данных во всех базах данных на вашем экземпляре SQL Server. Попробуйте и посмотрите, как она упрощает задачи управления базами данных!
Статья последний раз обновлялась: 2022-02-24