SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Как найти объект в базе данных SQL Server

ПУБЛИКАЦИИ  

По материалам статьи Ashish Kaushal: How to Find a SQL Server Database Object
Перевод Виталия Степаненко

Насколько часто мы - разработчики и администраторы SQL Server - вынуждены перебирать объекты в Enterprise Manager или открывать панель объектов в Query Analyzer, пытаясь найти таблицу или представление и не имея практически никакой информации, кроме почти правильного имени, и единственный путь убедиться, что это именно нужный нам объект - взглянуть на его структуру или текст. Конечно, такие вещи могут происходить не каждый день, но время от времени случаются (возможно, такого не происходит в идеальной ситуации, когда все базы данных хорошо документированы и все имена объектов соответствуют четким соглашениям по наименованию без каких-либо исключений, и более того, когда сотрудники никогда не увольняются).

Более привлекательным способом поиска объекта SQL Server, такого, как таблица, процедура или триггер, является запрос к системной таблице sysobjects в локальной базе данных (конечно, необходимо представлять, в какой базе данных располагается нужный объект).

Например:

Скрипт I

Select * From sysobjects Where name like ‘ClientInvoice%’

Выполнение этого запроса отобразит все объекты в текущей базе данных, чьи имена начинаются на "ClientInvoice". Если тип искомого объекта известен, тогда запрос может быть изменен, чтобы вывести только объекты этого типа, и чьи имена начинаются на "ClientInvoice". Такой вариант может вернуть гораздо меньший и более читабельный результирующий набор данных.

Например:

Скрипт II

Select * From sysobjects Where xtype = ‘U’ And name like ‘ClientInvoice%’
-- ‘U’ для пользовательской таблицы

Главным недостатком указанных методов является то, что таблица sysobjects относится к определенной базе данных. Если вы не знаете, какая база данных содержит объект, тогда запрос необходимо запускать во всех базах данных, чтобы найти объект.

Существует ли более простой путь написания запроса, который бы производил поиск во всех базах данных за один шаг, отыскивая определенный объекта и / или определенный тип объектов? Ответ - да, это можно сделать при помощи удобной процедуры sp_MSforeachdb.

Например:

Скрипт III

Exec sp_MSforeachdb 'Select * From ?..sysobjects where xtype= ''U'' And name like ''ClientInvoice% '''

sp_MSforeachdb - это недокументированная (значит, неподдерживаемая) процедура, доступная и в SQL Server 7, и в SQL Server 2000. Она принимает один строковый параметр, которым в нашем случае является скриптом II, но есть одно важное отличие - если мы внимательно посмотрим на скрипт III, то увидим "From ?..sysobjects" вместо "From sysobjects" в скрипте II.

Почему? Это важно, потому что sp_MSforeachdb использует внутри себя динамический SQL, и "?" - это шаблон имени базы данных, который заменяется на имя каждой базы данных в цикле, т.е. процедура в цикле последовательно вызывает таблицу sysobjects в каждой базе данных. Предположим, что у нас есть n баз данных, и если мы не поставим "?", то sp_MSforeachdb вместо цикла по n базам данных будет производить поиск в таблице sysobjects текущей базы данных (той, в которой мы запускаем запрос) n раз.

Скрипт IV

Теперь, когда мы знаем, что "?" является шаблоном имени базы данных, почему бы не попробовать написать скрипт, который выдаст результирующий набор данных с именем базы данных, именем объекта и типом объекта.

-- 1 часть
Declare @sqlstr nvarchar(200)

-- 2 часть
/* Удаление временной таблицы, если она существует */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table# tblDBObjects
/* Создание временной таблицы */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)

-- 3 часть
/* Присвоение строкового значения переменной */
Select @sqlstr = 'sp_msforeachdb ''Insert tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
/* Выполнение строки SQL */
Exec sp_executesql @sqlstr

-- 4 часть
/* Выборка данных из временной таблицы */
Select * From #tblDBObjects Where name like 'ClientInvoice%'
RETURN

Объяснение скрипта:

1 часть скрипта объявляет переменную типа nvarchar, т.к. строка, выполняемая процедурой sp_executeSQL должна иметь тип nvarchar.

2 часть проверяет, существует ли временная таблица с именем tblDBObjects. Если такая таблица существует, то она удаляется. После этого эта временная таблица #tblDBObjects создается вновь. '#' означает, что таблица должна быть временной, поэтому она создается в базе данных tempdb. Временная таблица автоматически удаляется, как только скрипт успешно завершает свою работу.

3 часть создает строку SQL, которая вставляет значения в таблицу #tblDBObjects из таблицы sysobjects каждой базы данных. Причиной использования этой строки и команды sp_ExecuteSQL является то, что она позволяет нам передать тип объекта в виде входного параметра, если мы захотим написать хранимую процедуру и передать имя объекта и тип объекта в виде входных параметров. Передача типов объектов уменьшит результирующий набор данных и может также ускорить выполнение операции в том случае, если приходится работать с множеством больших баз данных. Это будет рассмотрено в скрипте V.

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

Скрипт V

Пример хранимой процедуры, которая может использоваться для поиска объектов:

Create PROC FindObject_usp (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Вставляйте специальные символы (wildcards), если не требуется точный поиск.
-- Set @objname = '%' + @objname + '%' -- Лучше определять пользовательские специальные символы (custom wildcards) для входного параметра @objname
/* Удаление временной таблицы, если она существует */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* Создание временной таблицы */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR''''''' If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* Выполнение строки SQL */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* Если параметр @objname не передан, то результат все равно должен быть возвращен */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN

Этот скрипт создает хранимую процедуру, которая принимает 2 необязательных параметра - @objname (имя объекта, который надо найти) и @objtype (тип объекта, который надо найти). Типы объектов и их аббревиатуры могут быть найдены в разделе помощи о таблице sysobjects). Хранимая процедура FindObject_usp создает строки SQL различного вида в зависимости от типов данных, т.е., в зависимости от параметра @objtype. Если параметр @objtype не передан, то процедура выбирает все объекты из таблицы sysobjects и вставляет их во временную таблицу #tblDBObjects. Очевидно, что в случае больших баз данных, если тип объекта известен, передача параметра @objtype приводит к гораздо более быстрому выполнению запроса. Когда таблица #tblDBObjects заполнена, из нее выбираются данные при помощи параметра @objname, в том числе с использованием специальных символов.

Мы можем выполнить процедуру FindObject_usp, например, чтобы найти объект типа Check (ограничение), чье имя начинается на 'CK_B':

Exec FindObject_usp 'CK_B%', 'check'

или

Exec FindObject_usp1 'xp_%', Null

Заключение

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

[В начало]

Перевод: Виталия Степаненко  2004г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013