Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли одним запросом получить список всех баз?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Списко всех юзерских баз, у которых есть таблица mytable?
11 фев 15, 19:48    [17252193]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Смотря что вы вкладываете в понятие "один запрос".
11 фев 15, 19:49    [17252195]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Гавриленко Сергей Алексеевич
Смотря что вы вкладываете в понятие "один запрос".


Не, ну можно процедуру написать.

А вот такой запрос, чтобы из NET SqlCommand выполнить.
Типа такого
SELECT name,status FROM master.dbo.sysdatabases

Но, чтобы не весь список, а только содержащие таблицу mytable
11 фев 15, 19:52    [17252203]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
komrad
Member

Откуда:
Сообщений: 5261
Winnipuh
Гавриленко Сергей Алексеевич
Смотря что вы вкладываете в понятие "один запрос".


Не, ну можно процедуру написать.

А вот такой запрос, чтобы из NET SqlCommand выполнить.
Типа такого
SELECT name,status FROM master.dbo.sysdatabases

Но, чтобы не весь список, а только содержащие таблицу mytable


на вскидку:
exec sp_msforeachdb 'if exists (select 1 from [?].sys.objects where type=''U'' and name=''Orders'') select ''?'';'
11 фев 15, 19:58    [17252220]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
step_ks
Member

Откуда:
Сообщений: 936
SELECT name,status FROM master.dbo.sysdatabases WHERE object_id(quotename(name)+'.MySchema.MyTable') is not null
11 фев 15, 20:57    [17252386]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
step_ks
Member

Откуда:
Сообщений: 936
а точнее
SELECT name,status FROM master.dbo.sysdatabases WHERE object_id(quotename(name)+'.MySchema.MyTable','U') is not null
11 фев 15, 21:06    [17252420]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
step_ks,

а зачем статус опрашиваете?
если хоть одна база, например, в оффлайне, то уже на ней споткнется.
у меня это как раз первая, так что оно не успело ни строчки выдать:

Msg 942, Level 14, State 4, Line 1
Database 'db1' cannot be opened because it is offline.
11 фев 15, 22:34    [17252761]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
а, извиняюсь, ТС просто сам просил статус.
вот идея step_ks без статуса, но зато не падает на базах, к-ые offline, recovery_pending, etc:

with cte as 
(
select name,
       case state 
            when 0 then object_id(quotename(name)+'.MySchema.MyTable','U')
       end as flag
from master.sys.databases 
)

select name
from cte where flag is not null;
12 фев 15, 00:13    [17253116]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Все работает, спасибо, но есть одно НО:

если на одну из 20 баз у юзера нету прав - списка нету.
13 фев 15, 12:21    [17260059]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
вы меня канечно простите - НО нахрена ето вообще юзеру надо? Зачем ему список БД ?
13 фев 15, 12:22    [17260073]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
Winnipuh
Гавриленко Сергей Алексеевич
Смотря что вы вкладываете в понятие "один запрос".


Не, ну можно процедуру написать.

А вот такой запрос, чтобы из NET SqlCommand выполнить.
Типа такого
SELECT name,status FROM master.dbo.sysdatabases
Всё равно непонятно, что вы вкладываете в понятие "один запрос".

Из NET SqlCommand можно выполнить ровно тот код, что и в теле процедуры, только убрать слова "CREATE PROCEDURE"

Соответственно, пишите SELECT name FROM master.dbo.sysdatabases с правильным условием по status, из него собираете динамический SQL, который в нужных базах ищет те, в которых есть табличка mytable

И всё это выполняете "как один запрос из NET SqlCommand".
13 фев 15, 12:37    [17260225]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
Winnipuh
Все работает, спасибо, но есть одно НО:
если на одну из 20 баз у юзера нету прав - списка нету.

что за наезд?
у меня нет прав на все базы, но для моих объектов мне выдался список баз, куда я попадаю и где такая таблица есть.
или вы думаете, что можно вот так нагло получить список того, на что и прав нет?
куда тогда вообще все секьюрити летит, если бы можно было провернуть такое?
13 фев 15, 12:50    [17260346]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Winnipuh
Все работает, спасибо, но есть одно НО:

если на одну из 20 баз у юзера нету прав - списка нету.


HAS_DBACCESS(dbname)
13 фев 15, 15:42    [17261700]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
полный ответ с учетом пожелания ТС и подсказки Змея:
with cte as 
(
select name,
       case state 
            when 0 then object_id(quotename(name)+'.a.t','U')
       end as flag
from master.sys.databases 
)

select name, 'object exists' as result
from cte where flag is not null

union all

select name, 'no access'
from master.sys.databases 
where HAS_DBACCESS(name) = 0; 
13 фев 15, 19:17    [17263051]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
Winnipuh
Все работает, спасибо, но есть одно НО:
если на одну из 20 баз у юзера нету прав - списка нету.

извиняюсь за перерыв во времени,
для юзера с правами не везде и не на все все оказалось не совсем так.

если искомый объект в базе, куда юзер не отмаплен, не существует то
select object_id(quotename(name)+'.a.t','U')

выдает NULL, т.е. нет никакой ошибки, и весь запрос корректно завершается.
если же в базе, где юзера нет, такой объект есть, то код слетит с ошибкой
The server principal "l" is not able to access the database "err_log_3" under the current security context.

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

т.е. как ни крути, под юзером с урезанными правами инфо об искомом объекте выдается неполное:
в базе, куда доступ есть, NULL не указывает на отсутствие объекта, а всего лишь на то, что данный юзер
данный объект "не видит", т.е. может у него просто нет прав.

---
мне хотелось написать скрипт, анализирующий все возможные ситуации для юзера с уразанными правами.
в результате время идет, а воз и ныне там, но вот что стало ясно:
самый верный способ проверить объект -- это опросить его.
select count(*) -- вредно, т.к. возможен скан.
select top (1) -- не универсально, т.к. при пустой таблице просто 0 строк
(а я результат в таблицу кладу, т.е. меня 0 строк не устраивают).
короче, вот мой изврат, проще анализировать вручную:

+
set nocount on;

declare @sch_name sysname = N'dbo';
declare @obj_name sysname = N't';
declare @id int, @name sysname;
declare @res table (name sysname, has_access tinyint, cnt int);
declare @t_err table (name sysname, has_access tinyint, err nvarchar(4000));
declare @sql nvarchar(255);


declare cur cursor for 
select database_id, name from sys.databases 
open cur
fetch next from cur into @id, @name
while @@FETCH_STATUS =0
begin    
	set @sql = N'select ''' + @name + ''', HAS_DBACCESS(''' + @name + '''), count(*) from ' + 	           
	           quotename(@name) + '.' + quotename(@sch_name) + '.' + quotename(@obj_name)
	           + ';';
	   print @sql;        
	
	   begin try
		   insert into @res(name, has_access, cnt) exec (@sql);
	   end try
	   
		begin catch
		    insert into @t_err(name, has_access, err)
			select @name, HAS_DBACCESS(@name), error_message() as err 
		end catch	

	fetch next from cur into @id, @name
end
close cur
deallocate cur


select * from @res;	       
select * from @t_err;      


далее анализируем глазками:
select * from @res;

name has_access cnt
db1 1 0

объект есть :)
select * from @t_err; 

name has_access err
master 1 The SELECT permission was denied on the object 't' database 'master' schema 'dbo'.
tempdb 1 Invalid object name 'tempdb.dbo.t'.
err_log_3 0 The server principal "l" is not able to access the database "err_log_3" under the current security context.
AdventureWorksLT 0 Invalid object name 'AdventureWorksLT.dbo.t'.

все возможные 4 случая -- в последней таблице:
1 -- таблица есть, доступ к базе есть, прав на таблицу нет
2 -- таблицы нет, доступ к базе есть
3 -- таблица ЕСТь, доступа к базе нет
4 -- таблицы нет, доступа к базе нет

случаи 3,4 мне принципиально нравятся, т.к. по сообщению можно сообразить, существует ли объект в базе,
к к-ой у тебя вообще никакого доступа нет.
23 фев 15, 18:35    [17302103]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
о, проверю, но надо делать процедуру наверное
23 фев 15, 19:49    [17302328]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
o-o
полный ответ с учетом пожелания ТС и подсказки Змея:
with cte as 
(
select name,
       case state 
            when 0 then object_id(quotename(name)+'.a.t','U')
       end as flag
from master.sys.databases 
[b]where name not in
  (
    select name
    from master.sys.databases 
    where HAS_DBACCESS(name) = 0
  )[/b]
)

select name, 'object exists' as result
from cte where flag is not null

union all

select name, 'no access'
from master.sys.databases 
where HAS_DBACCESS(name) = 0; 


Всунул проверку внутрь, поскольку на первой же ошибке доступа выпадает пустое множество.
А так рабоатет.
27 фев 15, 16:04    [17321943]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли одним запросом получить список всех баз?  [new]
o-o
Guest
Winnipuh,
эх, не в этом дело :(
можно и вот так:
select name,
       case state 
            when 0 then 
				case HAS_DBACCESS(name) 
					   when 1 then isnull (cast(object_id(quotename(name)+'.a.t','U') as varchar(20)), 'no object')
					   else 'no access'
				end
            else 'db is not online'       
       end as flag
from master.sys.databases 

просто 'no object' ничего не значит,
вернее, означает, что данный юзер просто объект "не видит",
а нет объектa, или нет прав -- вы не узнаете.
надо именно что пытаться к объекту обратиться, даже в недоступной базе.
в недоступной базе
select object_id(quotename(name)+'.a.t','U'

дает ошибку ЛИШь В ТОМ СЛУЧАЕ, КОГДА ОБъЕКТ В БАЗЕ ЕСТь
27 фев 15, 17:57    [17322612]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить