При установке SQL Server всегда создаются четыре базы данных, содержащие системные таблицы:
Любая база данных, в том числе и перечисленные системные базы, содержит 18 системных таблиц, которые хранят информацию, определяющую структуру и организацию этой базы данных. В табл. 2.1 перечислены эти таблицы с кратким описанием их назначения. Мы специально не останавливаемся на перечислении колонок таблиц. Вы можете воспользоваться средствами SQL Server Enterprise Manager, чтобы получить информацию о колонках. Просто обратитесь к окну свойств требуемой таблицы либо откройте ее для просмотра.
Таблица 2.1. Системные таблицы, содержащиеся в каждой базе данных
| Название таблицы | Описание |
| Sysallocations | Содержит сведения о размещении объектов внутри базы данных |
| Syscolumns | В таблице перечислены поля всех таблиц и представлений, принадлежащих этой базе данных. Здесь также содержится информация о параметрах хранимых процедур |
| Syscomments | Таблица содержит определения объектов баз данных |
| Sysconstraints | Содержит соответствия между ограничениями (constraints) и объектами, которым они принадлежат |
| Sysdepends | В этой таблице хранятся ссылки на объекты, которые были использованы при определении других объектов баз данных |
| Sysfilegroups | В таблице перечислены все группы файлов, принадлежащие этой базе данных |
| Sysfiles | Здесь перечислены все файлы базы данных |
| Sysforeignkeys | Содержит информацию, определяющую ограничение FOREIGN KEY |
| Sysfulltextcatalogs | В таблице перечислены все полнотекстовые каталоги, присоединенные к базе данных |
| Sysindexes | Таблица используется для хранения информации об индексах и таблицах базы данных |
| Sysindexkeys | Таблица хранит сведения о ключах и колонках индексов |
| Sysmembers | В таблице перечислены все участники ролей базы данных |
| Sysobjects | Сведения обо всех объектах, создаваемых в базе данных, содержатся в этой системной таблице |
| Syspermissions | Таблица содержит информацию о предоставленных либо отклоненных правах на доступ к объектам базы данных для ролей, пользователей и групп пользователей |
| Sysprotects | В таблицу заносятся сведения о разрешениях, которые предоставляются либо отзываются посредством команд GRANTиREVOKE |
| Sysreferences | Содержит соответствия между ограничением FOREIGN KEYи колонками, на которые оно ссылается |
| Systypes | Все типы данных, как стандартные, так и пользовательские, хранятся в этой таблице |
| Sysusers | В таблице содержится список всех пользователей базы данных. Список включает пользователей SQL Server и Windows NT, роли SQL Server и группы пользователей Windows NT |
Поскольку системные таблицы содержат сведения, необходимые для успешного функционирования сервера, пользователь не должен напрямую изменять хранящуюся в них информацию. По умолчанию прямой доступ к системным таблицам и использованию Transact-SQL-команд SELECT, INSERT, UPDATE и DELETE запрещен. Для изменения данных необходимо использовать системные хранимые процедуры. Другой альтернативой является применение приложений, использующих SQL-DMO API. Microsoft вообще не рекомендует изменять значения в системных таблицах напрямую, настойчиво рекомендуя использовать хранимые процедуры, что гарантирует правильность и надежность выполняемых операций. Имеющиеся хранимые процедуры охватывают все возможные ситуации управления базами данных, так что необходимость прямого доступа к системным таблицам практически исключена. Кроме того, использование в прикладных приложениях хранимых процедур позволяет гарантировать, что работоспособность программы сохранится даже при изменении структуры системных таблиц в следующих версиях SQL Server.
Не нашли то, что искали? Воспользуйтесь поиском:
Системные базы данных и таблицы SQL Server 2000/7.0
(По материалам статьи Krishnan M Kaniappan на swynk.com «System Databases and Tables»)
Когда Вы устанавливаете SQL Server 7.0, автоматически создается четыре системные базы данных и две пользовательские базы Pubs и Northwind. Четыре системные базы данных играют жизненно важную роль для SQL Server — Master, Model, Msdb и Tempdb.
База данных Master:
Эта база данных хранит всю критическую для сервера информацию о конфигурации, учётных записях пользователей, запускаемых процессах, сообщениях об ошибках, системных хранимых процедурах, расположении главных файлов баз данных и т.д. При её повреждении, Вы можете использовать утилиту rebuildm.exe, которая восстанавливает базу Master и затем восстановить пользовательские базы. Старайтесь всегда иметь актуальную копию системной базы данных Master. Содержащиеся в ней системные таблицы можно разбить по следующим категориям:
| REMOTE SERVICES |
ACTIVE USER PROCESSES |
CURSORS |
| sysservers | sysprocesses | syscursorcolumns |
| sysremotelogins | syslockinfo | syscursortables |
| sysoledbusers | syslocks | syscursors |
| syslogins |
| CONFIGURATION | STORAGE | OTHERS |
| sysconfigures | sysdevices | syslanguages |
| syscurconfigs | sysaltfiles | sysmessages |
| syscurconfigs | ||
| sysdatabases | ||
| syscacheobjects | ||
| syscharsets |
Дополнительную информацию об этих таблицах можно найти в BOOKS ONLINE.
База данных Model:
Эта база данных используется, как шаблон для новых баз данных то есть, всякий раз, когда Вы создаете новую базу данных, объекты в этой базе данных копируется в новую базу данных. Например, если Вы создаете новую базу данных без указания её размера, SQL создаст её с размер Model, то есть 0.75 МБ и скопирует все объекты из образцовой базы в новую базу данных. Если Вы хотите исполнить некоторые схожие действия для всех вновь создаваемых пользовательских базах данных, Вы можете включать необходимые объекты, таблицы, процедуры и т. п. в Model, и тогда, каждая новая база данных также будет их содержать.
Ниже представлены некоторые часто встречающиеся действия, выполняемые в базе данных Model:
Включение определяемого пользователем типа данных, constraints, rules или defaults;
Включение пользователей, имеющих разрешения на всех базах данных;
Установка конфигурации базы данных по умолчанию и т.д.;
Далее представлен упорядоченный по группам список системных таблиц MODEL:
| USERS & PERMISSIONS |
OBJECTS | REFERENTIAL INTEGRITY |
STORAGE |
| sysmembers | sysfulltextcatalogs | sysforeignkeys | sysfiles1 |
| sysprotects | syscomments | sysreferences | sysfilegroups |
| sysusers | sysobjects | sysindexkeys | sysfiles |
| syspermissions | sysdepends | sysallocations | |
| sysconstraints | |||
| sysindexes | |||
| syscolumns | |||
| systypes |
Дополнительную информацию об этих таблицах можно найти в BOOKS ONLINE.
База данных Msdb:
Это одна из важных системных баз данных SQL, обеспечивающая его управление и обслуживание. База данных Msdb содержит задания (task-scheduling), обработку исключений, аварийное управление и информацию об операторах системы, то есть содержит информацию для всех операторов об их адресах электронной почты или номерах пейджера, а также информацию об истории по всем сеансам резервного копирования или восстановления баз данных.
Системные таблицы базы MSDB можно разбить по категориям следующим образом:
| BACKUP | SQL SERVER AGENT |
DTS | DATABASE MAINTENANCE |
| backupset | sysalerts | sysdtspackages | sysdbmaintplans |
| backupfile | sysnotifications | sysdtscategories | sysdbmaintplan_databases |
| backupmediaset | sysoperators | sysdbmaintplan_history | |
| backupmediafamily | sysjobhistory | sysdbmaintplan_jobs | |
| restorehistory | sysjobsteps | ||
| restorefilegroup | syscategories | ||
| restorefile | systaskids | ||
| sysjobs | |||
| sysjobschedules | |||
| sysdownloadlist | |||
| systargetservergroups | |||
| systargetservergroupmembers | |||
| systargetservers | |||
| sysjobservers |
Дополнительную информацию об этих таблицах можно найти в BOOKS ONLINE.
База данных Tempdb:
Следуя своему названию, эта база данных хранит все временные таблицы и промежуточные результаты group by, order by, distinct clause и т.д. Временные данные хранятся в Tempdb, пока хоть один пользователь подключён к SQL серверу. Все временные данные и таблицы будут уничтожены после отключения последнего пользователя от SQL сервера. Все глобальные переменные потеряют свои значения после перезагрузки SQL сервера или при его крахе. Tempdb автоматически расширяется после запуска сервера с 8.0 МБ для данных и 0.5 MB для журнала транзакций. Это происходит потому, что Tempdb создаётся каждый раз заново при запуске SQL сервера. Причём обязательно в системе должна существовать база данных MODEL.
В отличие от более ранних версий SQL Server, базы данных не хранятся в специализированных устройствах (devices), а запасены непосредственно в файлах, которые могут расти независимо от друг друга. Теперь нет никаких ограничений на размещение Master и Model в отдельном файле (то есть устройство Master для более ранних версий) который иногда создавал проблемы со свободным местом. В SQL Server 7.0 Вы не встретите таких проблем, потому что все базы данных могут размещаться в собственных наборах файлов, которые могут расти независимо друг от друга.
Обратите внимание: Вы не должны изменить значения непосредственно в system tables, тем более, если Вы не знаете архитектуру всех системных таблиц. Микрософт рекомендует не изменить system tables непосредственно, а установить нужные значения через системные хранимые процедуры, использующиеся для управления system tables посредством запросов T-SQL. В таком случае, Вы будете менять необходимые значения и параметры через представления (view), что существенно снизит риск краха SQL сервера из-за нарушения целостности информации в system tables. Хотя не существует ни каких ограничений на использование системных таблиц и Вы можете с ними работать, как и с таблицами в пользовательских базах данных. Существенным является также то, что system tables зависят от внутренней архитектуры SQL сервера и могут изменяться в последующих выпусках. Поэтому всегда предпочтительно не изменить значения непосредственно через system tables.
ОБЛАСТЬ ПРИМЕНЕНИЯ: 
SQL Server База данных SQL Azure 
Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
SQL Server SQL Server входят следующие системные базы данных. includes the following system databases.
| Системная база данных System database | Описание Description |
|---|---|
| База данных master master Database | В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server SQL Server . Records all the system-level information for an instance of SQL Server SQL Server . |
| База данных msdb msdb Database | Используется агентом SQL Server для планирования предупреждений и задач. Is used by SQL Server Agent for scheduling alerts and jobs. |
| База данных model model Database | Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server SQL Server . Is used as the template for all databases created on the instance of SQL Server SQL Server . Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward. |
| База данных Resource Resource Database | База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server SQL Server . Is a read-only database that contains system objects that are included with SQL Server SQL Server . Системные объекты физически хранятся в базе данных Resource , но логически отображаются в схеме sys любой базы данных. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. |
| База данных tempdb tempdb Database | Рабочее пространство для временных объектов или взаимодействия результирующих наборов. Is a workspace for holding temporary objects or intermediate result sets. |
Для отдельных баз данных и эластичных пулов Базы данных SQL Azure используются только базы данных master и tempdb. For Azure SQL Database single databases and elastic pools, only master Database and tempdb Database apply. Дополнительные сведения см. в разделе Что являет собою сервер Базы данных SQL Azure?. For more information, see What is an Azure SQL Database server. Описание базы данных tempdb в контексте Базы данных SQL Azure см. в разделе База данных tempdb в базе данных SQL. For a discussion of tempdb in the context of Azure SQL Database, see tempdb Database in Azure SQL Database. Для Управляемого экземпляра Базы данных Azure SQL применяются все системные базы данных. For Azure SQL Database Managed Instance, all system databases apply. См. дополнительные сведения об Управляемом экземпляре Базы данных SQL Azure. For more information on Managed Instances in Azure SQL Database, see What is a Managed Instance
изменение системных данных Modifying System Data
SQL Server SQL Server не поддерживает прямое обновление пользователями данных в таких системных объектах, как таблицы, системные хранимые процедуры и представления каталогов. does not support users directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Вместо этого SQL Server SQL Server предоставляет полный набор административных средств, позволяющих пользователям управлять всей системой, пользователями и объектами базы данных. Instead, SQL Server SQL Server provides a complete set of administrative tools that let users fully administer their system and manage all users and objects in a database. следующие основные параметры. These include the following:
Административные программы, например SQL Server Management Studio SQL Server Management Studio . Administration utilities, such as SQL Server Management Studio SQL Server Management Studio .
SQL-SMO API. SQL-SMO API. Этот программный интерфейс позволяет программистам включать любые административные возможности SQL Server SQL Server в разрабатываемые приложения. This lets programmers include complete functionality for administering SQL Server SQL Server in their applications.
Transact-SQL Transact-SQL . scripts and stored procedures. Можно использовать системные хранимые процедуры и DDL-инструкции Transact-SQL Transact-SQL . These can use system stored procedures and Transact-SQL Transact-SQL DDL statements.
Эти средства защищают приложения от изменений системных объектов. These tools shield applications from changes in the system objects. Например, иногда в целях поддержки новых возможностей, добавленных в новые версии SQL Server SQL Server , приходится изменять системные таблицы этих версий SQL Server SQL Server . For example, SQL Server SQL Server sometimes has to change the system tables in new versions of SQL Server SQL Server to support new functionality that is being added in that version. Приложения, выполняющие инструкции SELECT, которые ссылаются непосредственно на системные таблицы, часто зависят от старого формата этих таблиц. Applications issuing SELECT statements that directly reference system tables are frequently dependent on the old format of the system tables. Обновление сайтов до новой версии SQL Server SQL Server невозможно, пока для них не будут переписаны приложения, выполняющие выборку из системных таблиц. Sites may not be able to upgrade to a new version of SQL Server SQL Server until they have rewritten applications that are selecting from system tables. SQL Server SQL Server учитывает существующие системные хранимые процедуры, DDL и опубликованные интерфейсы SQL-SMO и работает, поддерживая обратную совместимость этих интерфейсов. considers the system stored procedures, DDL, and SQL-SMO published interfaces, and works to maintain the backward compatibility of these interfaces.
SQL Server SQL Server не поддерживаются триггеры, заданные для системных таблиц, поскольку они могут влиять на работу системы. does not support triggers defined on the system tables, because they might modify the operation of the system.
Системные базы данных не могут размещаться в общих каталогах UNC. System databases cannot reside on UNC share directories.
просмотр данных системной базы данных Viewing System Database Data
Не следует создавать инструкции Transact-SQL Transact-SQL , которые выполняют запросы непосредственно к системным таблицам, если только это не единственный способ получить данные, необходимые для приложения. You should not code Transact-SQL Transact-SQL statements that directly query the system tables, unless that is the only way to obtain the information that is required by the application. Приложения должны получать данные каталога и системные данные с помощью следующих средств: Instead, applications should obtain catalog and system information by using the following:
представлений системного каталога; System catalog views
интерфейса инструментария управления Windows (WMI); Windows Management Instrumentation (WMI) interface
функций каталога, методов, атрибутов или свойств данных API, использующихся в приложении, например ADO, OLE DB или ODBC; Catalog functions, methods, attributes, or properties of the data API used in the application, such as ADO, OLE DB, or ODBC.
Transact-SQL Transact-SQL встроенных функций и системных хранимых процедур. system stored procedures and built-in functions.