В качестве администратора баз данных SQL Server (DBA) у вас может быть набор процедур и представлений, которые вы часто используете для упрощения своих задач. Эти объекты обычно полезны и должны быть доступны во всех пользовательских базах данных. Хотя вы можете вручную устанавливать эти объекты в каждой базе данных, есть альтернативный подход, использующий представления INFORMATION_SCHEMA.
SQL Server 2000 представил набор специальных представлений, называемых представлениями INFORMATION_SCHEMA, которые находятся в базе данных MASTER. Эти представления имеют уникальное поведение – когда вы запрашиваете их, возвращаемые записи специфичны для текущего контекста базы данных. Например, если вы выполняете оператор SELECT на представлении INFORMATION_SCHEMA.TABLES в базе данных “pubs”, вы получите информацию о таблицах и представлениях в базе данных “pubs”. Аналогично, выполнение того же запроса в базе данных “Northwind” даст вам информацию, относящуюся к “Northwind”.
Как DBA, вы можете использовать это поведение, чтобы добавить свои собственные избранные запросы в эти представления INFORMATION_SCHEMA. Однако важно отметить, что добавление объектов в базу данных MASTER обычно не рекомендуется. Если вы решите использовать эту технику, следует принять следующие меры предосторожности:
- Сделайте резервную копию базы данных MASTER перед продолжением
- Сделайте резервную копию базы данных MASTER после завершения установки
- Убедитесь, что ваши объекты имеют имена, которые не могут конфликтовать с существующими именами объектов SQL Server
- Храните скрипты для создания ваших утилитарных объектов в безопасном месте, предпочтительно под управлением системы контроля версий
- Всегда удаляйте утилитарные объекты перед применением исправлений или пакетов обновлений для SQL Server
Для создания представления INFORMATION_SCHEMA вам необходимо временно разрешить обновления системных таблиц в базе данных MASTER. После создания представления следует отключить возможность обновления системных таблиц. Вот пример скрипта, который создает представление INFORMATION_SCHEMA для отображения примерного количества строк для каждой пользовательской таблицы в базе данных:
USE MASTER
GO
-- Разрешить обновления системных таблиц
EXEC sp_configure 'Allow Updates', 1
RECONFIGURE WITH OVERRIDE
GO
-- Если уже существует предыдущая версия представления, удалить ее
IF EXISTS(SELECT 1 FROM SysObjects WHERE Type='V' AND Name='TABLE_ESTIMATED_ROWS')
DROP VIEW INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS
GO
CREATE VIEW INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS
AS
SELECT object_name(id) AS TableName,
MAX(rowcnt) AS EstimatedRows
FROM dbo.sysindexes
WHERE indid < 2 -- кластеризованный индекс или запись таблицы
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND PERMISSIONS(id) != 0 -- Пользователь должен иметь хотя бы некоторые привилегии на объект.
GROUP BY object_name(id)
GO
-- Предоставить права SELECT для представления роли PUBLIC
GRANT SELECT ON INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS TO PUBLIC
GO
-- Запретить обновления системных таблиц.
EXEC sp_configure 'Allow Updates', 0
RECONFIGURE WITH OVERRIDE
GO
После создания представления вы можете просто запросить его в пользовательской базе данных, чтобы получить примерное количество строк для каждой таблицы:
SELECT * FROM INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS
Помимо представлений, вы также можете создавать утилитарные хранимые процедуры в SQL Server. Однако важно отметить, что если имя хранимой процедуры начинается с “sp_”, SQL Server сначала будет искать эту процедуру в базе данных MASTER, а затем в пользовательской базе данных. Поэтому считается плохой практикой использовать “sp_” в качестве префикса для имен хранимых процедур.
Чтобы сделать ваши утилитарные процедуры доступными, как системные хранимые процедуры, вы можете воспользоваться этим поведением. Однако, чтобы избежать конфликтов с хранимыми процедурами Microsoft, рекомендуется использовать соглашение об именовании, которое маловероятно будет конфликтовать. Например, вы можете использовать “sp_MyCompany” в качестве префикса для ваших утилитарных процедур.
Вот пример утилитарной процедуры, которая помечает все объекты определенного типа для повторной компиляции:
USE MASTER
GO
IF EXISTS (SELECT 1 FROM SysObjects WHERE Type='P' AND Name='sp_MyCompany_Recompile')
DROP PROC dbo.sp_MyCompany_Recompile
GO
CREATE PROC dbo.sp_MyCompany_Recompile
@ObjectType CHAR(1) = 'P' -- P = (по умолчанию) Процедуры, T = Триггеры, U = Пользовательские таблицы
AS
SET NOCOUNT ON
-- Разрешить выполнение этой процедуры только системным администраторам SQL Server.
IF IS_SRVROLEMEMBER('SysAdmin') = 0
BEGIN
RAISERROR (15003, -1, -1, N'SysAdmin')
RETURN
END
DECLARE @ObjectName sysname,
@sSQL VARCHAR(200)
SET @ObjectName = ''
WHILE @ObjectName IS NOT NULL
BEGIN
SELECT @ObjectName = MIN(Name)
FROM dbo.sysobjects
WHERE Type = CASE @ObjectType
WHEN 'U' THEN 'U'
WHEN 'T' THEN 'TR'
WHEN 'P' THEN 'P'
ELSE 'P'
END
AND Name > @ObjectName
AND OBJECTPROPERTY(Id, 'IsMSShipped') = 0
IF @ObjectName IS NOT NULL
BEGIN
EXEC sp_recompile @ObjectName
END
END
GO
GRANT EXECUTE ON sp_MyCompany_Recompile TO public
В этом примере хранимая процедура проверяет, является ли выполняющий пользователь членом роли SysAdmin, прежде чем разрешить выполнение процедуры. Это дополнительная мера безопасности, чтобы гарантировать, что только авторизованные пользователи могут выполнять процедуру.
Следует отметить, что вы не можете использовать представления INFORMATION_SCHEMA в утилитарных хранимых процедурах, находящихся в базе данных MASTER. В то время как утилитарная процедура, которая запрашивает dbo.sysobjects, будет получать доступ к таблице в текущей пользовательской базе данных, та, которая использует представление INFORMATION_SCHEMA, будет получать доступ только к информации из базы данных MASTER.
Существуют плюсы и минусы размещения утилитарных процедур и представлений в базе данных MASTER:
Плюсы:
- Объекты находятся только в одной базе данных, что облегчает обслуживание
- Существование процедур скрыто, если кто-то специально не ищет их
- Объекты доступны по всему серверу, что полезно для управления несколькими базами данных
Минусы:
- Необходимо быть осторожным, чтобы избежать конфликтов с объектами, поставляемыми Microsoft
- Перед установкой исправлений SQL Server утилитарные объекты должны быть удалены с сервера
- Если объекты нужно удалить, должны быть доступны скрипты для их повторного создания
- Поскольку эти процедуры доступны глобально, проверка безопасности внутри объектов должна быть тщательной
- Необходимо тщательно тестировать хранимые процедуры
В заключение, хотя обычно не рекомендуется добавлять объекты в базу данных MASTER, опытные DBA могут найти ситуации, когда гибкость или нарушение правил являются необходимыми. Если вы решите использовать описанные в этой статье техники, убедитесь, что следуете указанным ранее мерам предосторожности:
- Сделайте резервную копию базы данных MASTER перед продолжением
- Сделайте резервную копию базы данных MASTER после завершения установки
- Убедитесь, что ваши объекты имеют имена, которые не могут конфликтовать с существующими именами объектов SQL Server
- Храните скрипты для создания ваших утилитарных объектов в безопасном месте, предпочтительно под управлением системы контроля версий
- Всегда удаляйте утилитарные объекты перед применением исправлений или пакетов обновлений для SQL Server
Следуя этим рекомендациям, вы можете эффективно установить утилитарные процедуры и представления в SQL Server для упрощения своих задач в качестве DBA.