3.3. Получение сведений о базах данных и их файлах в текущем экземпляре сервера


Получить сведения о базах данных (как системных, так и пользовательских) и об их файлах можно при использовании множества разнообразных средств, входящих в состав SQL Server, которыми являются:

  • системные представления;
  • системные хранимые процедуры;
  • системные функции;
  • диалоговые средства компонента Management Studio.

  • Системное представление sys.databases

    Для того чтобы просмотреть список и характеристики всех баз данных, существующих в текущем экземпляре сервера, как пользовательских, так и системных, мы можем обратиться, пожалуй, к самому важному и информативному системному представлению просмотра каталогов sys.databases.
    Это представление отображает значения множества характеристик баз данных текущего экземпляра сервера. Представление возвращает одну строку для каждой базы данных. Рассмотрим только некоторые из столбцов, получаемых из этого представления, которые нам будут в первую очередь интересны.

  • name — содержит логическое имя базы данных.

  • database_id — идентификатор базы данных (целое число), автоматически присваиваемый базе данных системой при ее создании.

  • create_date — дата и время создания базы данных. Время указывается с точностью до миллисекунд.

  • collation_name — имя порядка сортировки по умолчанию для базы данных.

  • is_read_only — определяет, является ли база данных базой только для чтения:
  • 0 — база данных находится в режиме только для чтения (READ_ONLY);
    1 — база данных в режиме чтения и записи (READ_WRITE).

  • state — состояние базы данных:
  • 0 — ONLINE;
    1 — RESTORING;
    2 — RECOVERING;
    3 — RECOVERY_PENDING;
    4 — SUSPECT;
    5 — EMERGENCY;
    6 — OFFLINE.

  • state_desc — текстовое описание состояния базы данных: ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY, OFFLINE. Эта характеристика является, разумеется, производной от состояния базы данных (state).

  • Системное представление sys.master_files

    Другое полезное системное представление — sys.master_files. Оно позволяет получить детальный список всех баз данных и файлов, входящих в состав каждой базы данных, а также многие интересные характеристики этих файлов. 
    Наиболее важными для нас столбцами этого представления будут следующие.

  • database_id — идентификатор базы данных. Имеет то же значение, что и в представлении sys.databases.

  • file_id — идентификатор файла (тоже целое число). Первичный файл имеет идентификатор 1.

  • type — задает тип файла:
  • 0 — файл данных (ROWS);
    1 — журнал транзакций (LOG);
    2 — файловый поток (FILESTREAM).

  • type_desc — текстовое описание типа файла из столбца type: ROWS — файл данных, LOG — файл журнала транзакций, FILESTREAM — файловый поток.

  • data_space_id — идентификатор пространства данных (файловой группы), которому принадлежит файл данных. Для всех файлов журнала транзакций идентификатор имеет значение 0.

  • name — логическое имя файла, заданное в операторе CREATE DATABASE или присвоенное системой по умолчанию, если пользователь не указал логическое имя.

  • physical_name — путь к файлу, включая имя дискового устройства, и имя файла в операционной системе.

  • state — состояние файла:
  • 0 — ONLINE;
    1 — RESTORING;
    2 — RECOVERING;
    3 — RECOVERY_PENDING;
    4 — SUSPECT;
    6 — OFFLINE;
    7 — DEFUNCT.

  • state_desc — текстовое описание состояния файла, производное от состояния файла (state): ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, OFFLINE, DEFUNCT.

  • is_read_only — определяет, является ли файл файлом только для чтения:
  • 1 — файл READ_ONLY, только для чтения;
    0 — файл READ_WRITE, возможны операции чтения, добавления, изменения и удаления данных в этом файле.

  • size — размер файла в страницах. Напомню, что страница в файле данных имеет размер 8 Кбайт или, иными словами, 8192 байта.

  • max_size — указывает три возможных варианта: (1) возможность увеличения размера файла, (2) максимальный размер файла в страницах или (3) неограниченность размера файла. Может принимать следующие значения:
  • 0 — увеличение размера файла недопустимо;
    –1 — файл растет неограниченно, пока он не исчерпает объема всего дискового пространства или пока не достигнет допустимого предела (2 Тбайт для журнала транзакций или 16 Тбайт для файла данных);
    положительное число — указывает максимальный размер файла в страницах. Число 268435456 может быть указано только для файла журнала транзакций. Означает, что файл может расти до максимального размера в 2 Тбайт.

  • is_percent_growth — указывает, задается ли увеличение размера файла в процентах или в страницах:
  • 0 — увеличение размера указано в страницах;
    1 — увеличение размера файла указано в процентах от начального размера.

  • growth — указывает, будет ли увеличиваться размер файла:
  • 0 — файл не будет увеличиваться в размерах. Указанный размер не может изменяться;
    1 и более — размер файла будет при необходимости увеличиваться автоматически в соответствии с заданными параметрами при создании базы данных.

    Системное представление sys.database_files

    Представление sys.database_files позволяет получить список файлов и их характеристик только одной текущей базы данных, указанной в операторе USE.

    Вот некоторые характеристики, которые можно получить при вызове этого представления. Они в точности дублируют значения столбцов представления sys.master_files. Вкратце повторим эти значения.

  • file_id — идентификатор файла.

  • type — тип файла: 0 — файл данных (ROWS), 1 — журнал транзакций (LOG), 2 — файловый поток (FILESTREAM).

  • type_desc — текстовое описание типа файла: ROWS — файл данных, LOG — файл журнала транзакций, FILESTREAM — файловый поток.

  • data_space_id — идентификатор файловой группы, которой принадлежит файл.

  • name — логическое имя файла, явно заданное в операторе CREATE DATABASE или присвоенное системой по умолчанию.

  • physical_name — путь к файлу, включая имя дискового устройства, каталоги и имя файла в операционной системе.

  • state — состояние файла:
  • 0 — ONLINE;
    1 — RESTORING;
    2 — RECOVERING;
    3 — RECOVERY_PENDING;
    4 — SUSPECT;
    6 — OFFLINE.
    7 — DEFUNCT.

  • state_desc — текстовое описание состояния файла, производное от состояния файла (state): ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, OFFLINE, DEFUNCT.

  • is_read_only — определяет, является ли файл файлом только для чтения:
  • 1 — файл READ_ONLY, только для чтения;
    0 — файл READ_WRITE, возможны операции чтения, удаления и обновления данных в этом файле.

  • size — размер файла в страницах.

  • max_size — указывает возможность увеличения размера файла, максимальный размер файла в страницах или неограниченность размера файла. Может принимать следующие значения:
  • 0 — увеличение размера файла недопустимо;
    1 — файл растет неограниченно, пока не исчерпает всего дискового пространства или не достигнет допустимого предела (2 Тбайта для журнала транзакций или 16 Тбайт для файла данных);
    положительное число — указывает максимальный размер файла в страницах.

  • is_percent_growth — указывает, задается ли увеличение размера файла в процентах или в страницах:
  • 0 — увеличение размера указано в страницах;
    1 — увеличение размера файла указано в процентах от начального размера.

  • growth — указывает, будет ли увеличиваться размер файла:
  • 0 — файл не будет увеличиваться в размерах. Указанный размер не может изменяться;
    1 и более — размер файла будет при необходимости увеличиваться автоматически в соответствии с заданными параметрами при создании базы данных.

    Системное представление sys.filegroups

    Системное представление sys.filegroups позволяет получить некоторые данные о файловых группах текущей базы данных, указанной в операторе USE. Вот некоторые столбцы этого представления, которые могут быть нам интересны.

  • name — название файловой группы. Первой всегда будет первичная файловая группа PRIMARY.

  • type — тип. Для файловых групп имеет значение FG.

  • type_desc — текстовое описание типа. Для файловой группы имеет значение ROWS_FILEGROUP.

  • is_read_only — указывает, является ли файловая группа группой только для чтения:
  • 0 — файловая группа доступна для чтения и записи, т. е. все файлы, входящие в состав этой файловой группы, доступны для чтения и записи;
    1 — файловая группа только для чтения.

    * * *

    Это, пожалуй, наиболее важные и полезные представления, которые вы будете использовать в вашей повседневной жизни. Давайте теперь очень кратко рассмотрим и некоторые другие средства, которые вам могут оказаться полезными при работе с базами данных в SQL Server. 

    Другие средства получения сведений об объектах базы данных 

    Мы рассмотрели некоторые средства, позволяющие получить детальные сведения о характеристиках существующих баз данных, их файлах и файловых группах. 
    Имеет смысл сказать несколько слов и о других средствах, которые мы с вами будем использовать в ближайшем будущем.

    Системные представления

    Представления для получения сведений об объектах базы данных.

  • sys.schemas — возвращает сведения о схемах базы данных. Каждая база данных может содержать более двух миллиардов схем. Надо сказать, что "схема" (schema) в SQL Server, это совсем не то, что схема в некоторых других системах управления базами данных. О схемах мы поговорим в разд. 3.8.

  • sys.database_permissions — возвращает сведения о полномочиях в базе данных.
  • Вопросы безопасности в SQL Server решаются довольно жестко и очень разумно. Специалистам по системам безопасности, в том числе и в базах данных, следует этим делам уделить достаточно серьезное внимание.

  • sys.database_principals — возвращает сведения о принципалах (владельцах или, иными словами, участниках доступа к базам данных и их объектам) в базе данных. Это опять же связано с вопросами безопасности в базах данных.

  • sys.database_role_members — возвращает сведения о членах (участниках) ролей в базе данных. И роли базы данных при правильном их использовании могут быть хорошим средством для повышения безопасности баз данных.
  • Другими системными представлениями просмотра каталогов для объектов, скажем так, "детального уровня", являются следующие далее представления.

  • sys.tables — возвращает сведения о таблицах базы данных. Здесь даются сведения обо всех таблицах текущей базы данных.

  • sys.views — возвращает сведения о представлениях в базе данных. Каждая база данных может содержать множество различных представлений. Представления позволяют упростить задачу пользователя по получению данных из одной или нескольких базовых таблиц. Представления позволяют "скрыть" от не очень профессионального (или слишком ленивого) пользователя все сложности, связанные с составлением запроса к данным базы данных для получения необходимых результатов.

  • sys.indexes — возвращает сведения об индексах базы данных. Индекс — это замечательный объект реляционной базы данных, который в одно и то же время может резко ухудшить работу с данными в базе данных, а может при правильном проектировании и сильно повысить производительность при выборке и упорядочении данных базы. Однако добавление к таблицам новых индексов никак не может улучшить временные характеристики при выполнении операций добавления или изменения данных, если в процесс изменения включены столбцы, входящие в состав индекса.

  • sys.events — возвращает сведения о событиях базы данных. Событие — это очень интересное и полезное средство при работе с базами данных. События позволяют синхронизировать работу программ, одновременно использующих одну и ту же базу, и иногда избежать некоторых ошибок в работе пользователей с базой данных.

  • sys.types — возвращает сведения о системных и пользовательских типах данных.

  • sys.columns — возвращает сведения о столбцах таблиц и представлений.
  • Для обращения к системным представлениям, как и к другим представлениям в базе данных, мы используем оператор SELECT, синтаксис которого и варианты применения будем подробно рассматривать в этой главе и в последующих главах.

    Системные хранимые процедуры

    Для обращения к хранимым процедурам, как системным, так и к пользовательским, используется оператор EXECUTE. Примеры таких обращений мы вскоре рассмотрим.

    Часто используются следующие хранимые процедуры.

  • sp_databases — предоставляет список баз данных.

  • sp_stored_procedures — возвращает список хранимых процедур.

  • sp_help — возвращает список различных объектов базы данных, типов данных, определенных пользователем или поддерживаемых системой SQL Server.

  • sp_helplogins — возвращает список регистрационных имен пользователей (login).

  • sp_helptext — дает возможность получить на языке Transact-SQL тексты, описывающие системные хранимые процедуры, триггеры, вычисляемые столбцы, ограничения CHECK для столбцов таблиц.

  • sp_changedbowner — позволяет изменить владельца базы данных.

  • sp_configure — позволяет изменить некоторые режимы системы.

  • Системные функции

    Помимо системных представлений просмотра каталогов и системных хранимых процедур в SQL Server присутствуют системные функции.
    Чтобы просмотреть логические имена файлов любой базы данных, системной или пользовательской, можно вызвать системную функцию FILE_NAME().

    Для получения идентификатора базы данных, который присваивается ей при ее создании, используется функция DB_ID(). Очень удобная и довольно часто применяемая функция. Ее регулярно будем использовать как в этой главе, так и в реальной жизни.

    Имя базы данных можно получить при вызове функции DB_NAME(), которой передается в качестве параметра идентификатор базы данных.

    Функция FILE_ID() для текущей базы данных возвращает идентификатор файла базы данных по указанному логическому имени этого файла.

    Функция FILEGROUP_ID() возвращает идентификатор файловой группы, заданной ее именем.

    Функция FILEGROUP_NAME(), наоборот, по идентификатору файловой группы возвращает ее имя.

    * * *

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

    Похожие новости

    Комментариев 0

    Информация
    Посетители, находящиеся в группе Гости, не могут оставлять комментарии к данной публикации.