Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Доброе утро!
Есть сервер с определённым количеством разных баз. Список их названий вызывала процедурой
select name from master..sysdatabases

Нужно все их протестировать, то есть написать хранимую процедуру, где каждая база будет поочередно открываться, внутри неё делаться запрос, результат запроса записываться в какую-нибудь таблицу, потом база будет закрываться, открываться следующая, и т.д., то есть нужно установить курсор.
Не подскажете, как это сделать правильно?
Спасибо
5 июн 13, 08:27    [14392226]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
1. sp_msforeachdb
2. declare cursor
3. F1 по первым двум пунктам.
5 июн 13, 08:31    [14392237]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
klara1
Доброе утро!
Есть сервер с определённым количеством разных баз. Список их названий вызывала процедурой
select name from master..sysdatabases

Нужно все их протестировать, то есть написать хранимую процедуру, где каждая база будет поочередно открываться, внутри неё делаться запрос, результат запроса записываться в какую-нибудь таблицу, потом база будет закрываться, открываться следующая, и т.д., то есть нужно установить курсор.
Не подскажете, как это сделать правильно?
Спасибо


Как сказали выше:

EXEC sp_MSForEachDB 'Текст вашего запроса'
Например,
EXEC sp_MSForEachDB 'SELECT * FROM sys.indexes'
5 июн 13, 09:02    [14392331]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Сергей Викт.,

прошу прощения.

EXEC sp_MSForEachDB 'USE [?];
SELECT * FROM sys.indexes'
5 июн 13, 09:56    [14392607]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Сергей Викт.,

вот спасибо! Просто замечательно. Как бы ещё в результат запроса имя базы впихнуть...
5 июн 13, 11:21    [14393138]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Например,
EXEC sp_MSForEachDB 'USE [?];
insert into basa1.dbo.prottt(idc,idf,auto_edit) select idc,idf,auto_edit from tab2'

вот если у таблицы prottt? напрмер, есть поле basa (имя базы), можно этим запросом скопировать в таблицу prott имя текущей базы, из которой идёт отбор?
5 июн 13, 11:27    [14393194]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Crimean
Member

Откуда:
Сообщений: 13147
может, так?

EXEC sp_MSForEachDB 'SELECT top 10 ''?'' as DbName , * FROM ?.sys.indexes where object_id in (select id from ?..sysobjects where xtype = ''u'')'
5 июн 13, 11:28    [14393197]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Select Db_Name(). конечно же! Спасибо за помощь)
5 июн 13, 11:31    [14393216]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Кстати, sp_MSForEachDB не документированная процедура, а тут еще пишут, что иногда вызывает ошибки.
Aaron Bertrand
Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.
5 июн 13, 12:04    [14393474]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
а если открыть курсором, без sp_MSForEachDB ? через таблицу sysdatabases ?
6 июн 13, 11:17    [14398653]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
а если открыть курсором, без sp_MSForEachDB ? через таблицу sysdatabases ?

А вы думаете, что sp_MSForEachDB делает по-другому ? Каким то секретным способом ?
6 июн 13, 11:19    [14398669]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Просто он не открывает те базы, которые используюится в настоящее время другими пользователями. Это можно как-то обойти?
6 июн 13, 11:22    [14398682]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
Просто он не открывает те базы, которые используюится в настоящее время другими пользователями.

И с чего бы "ему"(кстати кому ему) так делать ? И что такое "открывать базу" ?
6 июн 13, 11:24    [14398698]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
это недокументированная процедура.
Для перебора всех баз данных используется курсор. Не выбираются базы данных, недоступные на данный момент, находящиеся в режиме single user(кроме той к которой присоединен пользователь) и базы, к которым у пользователя запустившего процедуру нет прав доступа.
https://www.sql.ru/articles/mssql/02030101undocumentedsql.shtml#sp_MSforeachdb

Проверила - действительно, работает только с теми базами, которые не открыты
6 июн 13, 11:27    [14398715]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
Проверила - действительно, работает только с теми базами, которые не открыты

И вы покажете, как вы это проверили ?
6 июн 13, 11:28    [14398721]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
Одна из баз сервера была открыта другим пользователем, результаты запроса по ней не выдались. После закрытия её другим пользователем результат выдался.
А что, Вы считаете, это неверно? Почему тогда такие ошибки?
6 июн 13, 11:31    [14398741]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
Одна из баз сервера была открыта другим пользователем, результаты запроса по ней не выдались. После закрытия её другим пользователем результат выдался.

А вы читали цитату, которую привели ?
"Не выбираются базы данных, недоступные на данный момент, находящиеся в режиме single user(кроме той к которой присоединен пользователь) и базы, к которым у пользователя запустившего процедуру нет прав доступа."
6 июн 13, 11:33    [14398749]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
вот попробовала курсором, но не работает
use [basa22]
DECLARE @databasename varchar(250)
set @databasename=Db_Name()

	DECLARE	db_names_cursor CURSOR FOR
	SELECT	name
	FROM master..sysdatabases
	WHERE	name LIKE 'oe%'

	OPEN db_names_cursor
	FETCH NEXT FROM db_names_cursor INTO @databasename
	WHILE (@@fetch_status =0)
	BEGIN
	if exists (select name, dbid from master..sysdatabases where name =  @databasename)
 	begin	
EXEC 
 ('use [' + '@databasename' + ']')
if exists (select * from dbo.sysobjects where name = 'rrr' and xtype='U')insert into nereglament.dbo.tab2(basa,pole1,pole2) select Db_Name(),pole1,pole2, from rrr

end
 FETCH NEXT FROM db_names_cursor INTO @databasename

end
end

CLOSE db_names_cursor
DEALLOCATE db_names_cursor

надо открыть все базы и выбрать из них данные для таблицы. Не работает, в чём моя ошибка, подскажите пожалуйста??
6 июн 13, 13:54    [14399735]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
Не работает, в чём моя ошибка, подскажите пожалуйста??

В том, что вы думаете, что EXEC ('use [' + '@databasename' + ']') меняет контекст текущей базы
6 июн 13, 13:56    [14399744]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
одним словом, таким образом не открыть выбранную курсором базу? Это можно сделать иначе, пользуясь данными моего запроса?
6 июн 13, 14:00    [14399771]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
прикол, да?
Guest
klara1,

вот выполните такое для просветления:
use master;
exec ('use msdb; select db_name() as real_msdb');
select DB_NAME() as tipa_msdb;
6 июн 13, 14:01    [14399775]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
klara1
одним словом, таким образом не открыть выбранную курсором базу?

Нет команды "открыть базу" - это вами придуманный термин. Наверное foxpro-ное прошлое ?
Команда USE меняет текущий контекст базы
6 июн 13, 14:03    [14399791]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Может быть конечно, скажу глупость, но вы можете обращаться к таблицам любой базы данных в рамках одного инста как БД.схема.Объект. Не использую никаких use DB
6 июн 13, 14:22    [14399941]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
klara1
Member

Откуда:
Сообщений: 79
всё поняла, построила запрос по-другому


DECLARE @databasename varchar(250)
DECLARE @Command varchar(1000)
SELECT @databasename = NAME FROM sys.databases WHERE name like 'oe%'
SET @Command = 'USE [' + @DatabaseNAME + '];-- if exists (select * from dbo.sysobjects where name = ''tab1'' and xtype=''U'')insert into basa1.dbo.tab2(bas,pole1,pole2,) select Db_Name(),pole1,pole2 from tab1;---'


DECLARE	db_names_cursor CURSOR FOR
SELECT	name
FROM sys.databases


OPEN db_names_cursor
FETCH NEXT FROM db_names_cursor INTO @databasename
WHILE (@@fetch_status =0)
BEGIN
if exists (select name from sys.databases where name =  @databasename)
begin	
EXECUTE(@Command)

end
end

CLOSE db_names_cursor
DEALLOCATE db_names_cursor


теперь ошибок не выдаёт, но почему-то надолго зависает, не прорабатывает до конца. Не подскажете, где ошибка?
6 июн 13, 15:06    [14400243]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью процедуры открыть поочередно все базы сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
klara1
всё поняла, построила запрос по-другому


DECLARE @databasename varchar(250)
DECLARE @Command varchar(1000)
SELECT @databasename = NAME FROM sys.databases WHERE name like 'oe%'
SET @Command = 'USE [' + @DatabaseNAME + '];-- if exists (select * from dbo.sysobjects where name = ''tab1'' and xtype=''U'')insert into basa1.dbo.tab2(bas,pole1,pole2,) select Db_Name(),pole1,pole2 from tab1;---'


DECLARE	db_names_cursor CURSOR FOR
SELECT	name
FROM sys.databases


OPEN db_names_cursor
FETCH NEXT FROM db_names_cursor INTO @databasename
WHILE (@@fetch_status =0)
BEGIN
if exists (select name from sys.databases where name =  @databasename)
begin	
EXECUTE(@Command)

end
FETCH NEXT FROM db_names_cursor INTO @databasename
end

CLOSE db_names_cursor
DEALLOCATE db_names_cursor


теперь ошибок не выдаёт, но почему-то надолго зависает, не прорабатывает до конца. Не подскажете, где ошибка?

Вы не добавили
FETCH NEXT FROM db_names_cursor INTO @databasename
в теле цикла
6 июн 13, 15:13    [14400311]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить