Как выполнить T-SQL для нескольких баз данных, имена которых хранятся в таблице
Эй парни, таким образом, вот соглашение.
У меня есть несколько баз данных (SqlServer 2005) на том же сервере с той же схемой, но различными данными.
У меня есть одна дополнительная база данных, которая имеет одну таблицу, хранящую названия упомянутых баз данных.
Таким образом, то, что я должен сделать, должно выполнить итерации по тем базам данных имени и на самом деле "переключиться" на каждого (использование [dbname]) и выполнить сценарий T-SQL. Я ясен?
Позвольте мне дать Вам пример (упрощенный от реального):
CREATE TABLE DatabaseNames
(
Id int,
Name varchar(50)
)
INSERT INTO DatabaseNames SELECT 'DatabaseA'
INSERT INTO DatabaseNames SELECT 'DatabaseB'
INSERT INTO DatabaseNames SELECT 'DatabaseC'
Предположите, что DatabaseA, DatabaseB и DatabaseC являются реальными существующими базами данных. Так скажем, я должен создать новый SP на тех DBS. Мне нужен некоторый сценарий, что циклы по тем базам данных и выполняют сценарий T-SQL, который я указываю (возможно, сохраненный на varchar переменной или везде, где).
Какие-либо идеи?
Спасибо!
5 ответов
Я думаю, что это вообще невозможно в TSQL, поскольку, как указывали другие,
вам сначала понадобится как оператор USE для изменения базы данных,
, за которым следует оператор, который вы хотите выполнить, который, хотя и не указан, является оператором DDL, который должен быть первым в пакете.
Более того, вы не можете иметь GO в строке для ВЫПОЛНЕНИЯ.
Я нашел решение командной строки, вызывающее sqlcmd:
for /f "usebackq" %i in
(`sqlcmd -h -1 -Q
"set nocount on select name from master..sysdatabases where status=16"`)
do
sqlcmd -d %i -Q "print db_name()"
Образец кода использует текущую учетную запись Windows для запроса всех активных баз данных из Master (замените свое собственное соединение и запрос для баз данных) и выполняет буквальную команду TSQL для каждой базы данных. таким образом найдено. (разрывы строк только для ясности)
Взгляните на параметры командной строки sqlcmd . Вы также можете передать ему файл TSQL.
Если вы хотите разрешить выбор баз данных вручную, ознакомьтесь с SSMS Tools Pack .
Используйте команду USE и повторяйте свои команды
Пс. Посмотрите, как использовать USE с параметром здесь
Этот метод требует, чтобы вы поместили ваш SQL-скрипт для выполнения в каждой БД в переменной, но он должен работать.
DECLARE @SQLcmd varchar(MAX)
SET @SQLcmd ='Your SQL Commands here'
DECLARE @dbName nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
FETCH c INTO @dbName
IF @@fetch_status <> 0 BREAK
EXEC('USE [' + @dbName + '] ' + @SQLcmd )
END
CLOSE c
Кроме того, как отмечали некоторые. Такой подход проблематичен, если вы хотите запустить команду, которая должна быть единственной в пакете.
Вот альтернатива для этой ситуации, но она требует большего количества разрешений, чем многие администраторы баз данных могут вам требовать, и требует, чтобы вы поместили свой SQL в отдельный текстовый файл.
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT dbName FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
FETCH c INTO @dbName
IF @@fetch_status <> 0 BREAK
exec master.dbo.xp_cmdshell 'osql -E -S '+ @@SERVERNAME + ' -d ' + @dbName + ' -i c:\test.sql'
END
CLOSE c
DEALLOCATE c
Самый простой способ:
DECLARE @stmt nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT 'USE [' + Name + ']' FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
FETCH c INTO @stmt
IF @@fetch_status <> 0 BREAK
SET @stmt = @stmt + ' ' + @what_you_want_to_do
EXEC(@stmt)
END
CLOSE c
DEALLOCATE c
Однако очевидно, что он не будет работать для операторов, которые должны быть первым оператором в пакете, например CREATE PROCEDURE. Для этого вы можете использовать SQLCLR. Создайте и разверните такой класс:
public class StoredProcedures {
[SqlProcedure(Name="exec_in_db")]
public static void ExecInDb(string dbname, string sql) {
using (SqlConnection conn = new SqlConnection("context connection=true")) {
conn.Open();
using (SqlCommand cmd = conn.CreateCommand()) {
cmd.CommandText = "USE [" + dbname + "]";
cmd.ExecuteNonQuery();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
}
Затем вы можете сделать
DECLARE @db_name nvarchar(200)
DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT Name FROM DatabaseNames
OPEN c
WHILE 1 <> 0 BEGIN
FETCH c INTO @@db_name
IF @@fetch_status <> 0 BREAK
EXEC exec_in_db @db_name, @what_you_want_to_do
END
CLOSE c
DEALLOCATE c
Вы можете сделать это с помощью недокументированной хранимой процедуры sp_MSforeachdb .