Робота з системним каталогом і представленнями каталогу. Робота з системними процедурами,що зберігаються

Работа с системным каталогом и представлениями каталога

Представления каталога содержат информацию, использующуюся ядром базы данных SQL Server. Они предоставляют наиболее общий интерфейс метаданных каталога, са мый прямой путь доступа к этой информации и простейший способ работы с ней. Все доступные для пользователей метаданные в системном каталоге отображаются через представления каталога, которые не содержат информацию о репликации, резервном копировании, плане обслуживания БД и метаданных службы SQL Server Agent.

Представления каталога соответствуют общей концепции иерархии объектов, в которой объекты более низкого уровня наследуют атрибуты объектов более вы сокого уровня. Некоторые представления каталога наследуют столбцы (поскольку столбцы таблицы — атрибуты хранящихся в ней сущностей) из других представлений каталога. Например, представление каталога Tables (Таблицы) наследует столбцы представления каталога Objects (Объекты). Таким образом, к столбцам представ ления каталога Tables (Таблицы) добавляются все столбцы представления каталога Objects (Объекты). В табл. 4-1 перечислены представления каталога SQL Server 2005 и описывается их назначение.

Тип представлений	Описание	Ключевые представления каталога
CLR Assembly Catalog	Сборка общеязыковой исполняющей среды	sys.assemblies, sys.assembly_files, sys.assembly_references
Databases and Files Catalog	БД, файлы БД и устройства резервного копирования, ассоциированные с экземпляром SQL Server	sys.backup_devices, sys.database_files, sys.databases, sys.master_files
Database Mirroring Catalog	Функции, которые сервер-свидетель выполняет в качестве партера в процессе зеркального отображения БД	sys.database_mirroring_witnesses
Data Spaces and Full-Text Catalog	Группы файлов, схемы секционирования и полнотекстовые каталоги	sys.data_spaces, sys.destination_data_spaces, sys.filegroups, sys.fulltext_catalogs, sys.partition_schemes
Endpoints Catalog	Конечные точки, используемые для зеркального отображения, обмена сообщениями службой Service Broker и веб-служб	sys.database_mirroring_endpoints, sys.endpoint_webmethods, sys.endpoints, sys.http_endpoints, sys.service_broker_endpoints, sys.soap_endpoints, sys.tcp_endpoints, sys.via_endpoints
Extended Properties Catalog	Расширенные свойства и класс объектов, от которых они происходят	sys.extended_properties
Linked Servers Catalog	Связанные или удален ные серверы и относя щиеся к ним учетные записи	sys.linked_logins, sys.remote_logins, sys.servers
Messages (for Errors) Catalog Objects Catalog	Сообщения об ошибках, определенные системой и пользователем Высокоуровневые объекты БД	sys.messages sys.allocation_units, sys.assembly_modules, sys.check_constraints, sys.columns, sys.computed_columns, sys.default_constraints, sys.event_notifications, sys.events, sys.extended_procedures, sys.foreign_key_columns, sys.foreign_keys, sys.fulltext_index_columns, sys.fulltext_indexes, sys.identity_columns, sys.index_columns, sys.indexes, sys.key_constraints, sys.numbered_procedures, sys.numbered_procedure_parameters, sys.objects, sys.parameters, sys.partitions, sys.procedures, sys.service_queues, sys.sql_dependencies, sys.sql_modules, sys.stats_columns, sys.stats, sys.synonyms, sys.tables, sys.traces, sys.trigger_events, sys.triggers, sys.views
Partition Function Catalog	Функции секционирования, их параметры и диапазон значений	sys.partition_functions, sys.partition_parameters, sys.partition_range_values
Scalar Types Catalog	Пользовательские скалярные типы данных для сборок общеязыко вой исполняющей сре ды, а также другие ска лярные типы данных, пользовательские и системные	sys.assembly_types, sys.types
Schemas Catalog	Схемы БД	sys.schemas
Security Catalog	Атрибуты безопасности и их значения уровня сервера и БД, а также атрибуты безопасности шифрования	Уровня БД: sys.database_permissions, sys.database_principals, sys.database_role_members. Уровня сервера: sys.server_permissions, sys.server_principals, sys.server_role_members, sys.sql_logins. Шифрования: sys.asymmetric_keys, sys.certificates, sys.credentials, sys.crypt_properties, sys.key_encryptions, sys.symmetric_keys
Service Broker Catalog	Конечные точки и компоненты обмена сообщениями службы Service Broker	sys.conversation_endpoints, sys.conversation_groups, sys.remote_service_bindings, sys.service_contract_message_usages, sys.service_contract_usages, sys.routes, sys.service_contracts, sys.service_message_types, sys.services, sys.transmission_queue
Server-Wide Configuration Catalog	Значения параметров конфигурации сервера	sys.configurations, sys.fulltext_languages, sys.trace_categories, sys.trace_columns, sys.trace_event_bindings, sys.trace_events, sys.traces, sys.trace_subclass_values
XML Schemas (XML Type System) Catalog	Компоненты и значения схемы XML	sys.xml_indexes, sys.xml_schema_attributes, sys.xml_schema_collections, sys.xml_schema_component_placements, sys.xml_schema_components, sys.xml_schema_elements, sys.xml_schema_facets, sys.xml_schema_model_groups, sys.xml_schema_namespaces, sys.xml_schema_types, sys.xml_schema_wildcard_namespaces, sys.xml_schema_wildcards

В табл. 4-2 дан перечень сопоставлений системных таблиц SQL Server 2000 и системных представлений SQL Server 2005. Элементы перечня организованы по типам баз данных и представлений: за сопоставлениями для системной БД master следуют сопоставления для всех остальных БД.

Табл. 4-2. Сопоставление системных таблиц SQL Server 2000 и системных представлений SQL Server 2005

Системная таблица SQL Server 2000	Системное представление SQL Server 2005	Тип представления SQL Server 2005
БД master
sysaltfiles	sys.master_files	Каталог
syscacheobjects	sys.dm_exec_cached_plans	Динамическое управление
syscharsets	sys.syscharsets	Обратная совместимость
sysconfigures	sys.configurations	Каталог
syscurconfigs	sys.configurations	Каталог
sysdatabases	sys.databases	Каталог
sysdevices	sys.backup_devices	Каталог
syslanguages	sys.languages	Обратная совместимость
syslockinfo	sys.dm_tran_locks	Динамическое управление
syslocks	sys.dm_tran_locks	Динамическое управление
syslogins	sys.server_principals	Каталог
sysmessages	sys.messages	Каталог
sysoledbusers	sys.linked_logins	Каталог
sysopentapes	sys.dm_io_backup_tapes	Динамическое управление
sysperfinfo	sys.dm_os_performance_counters	Динамическое управление
sysprocesses	sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests	Динамическое управление
sysremotelogins	sys.remote_logins	Каталог
sysservers	sys.servers	Каталог
Все базы данных
syscolumns	sys.columns	Каталог
syscomments	sys.sql_modules	Каталог
sysconstraints	sys.check_constraints, sys.default_constraints, sys.key_constraints, sys.foreign_keys	Каталог
sysdepends	sys.sql_dependencies	Каталог
sysfilegroups	sys.filegroups	Каталог
sysfiles	sys.database_files	Каталог
sysforeignkeys	sys.foreign_keys	Каталог
sysfulltextcatalogs	sys.fulltext_catalogs	Каталог
sysindexes	sys.indexes	Каталог
sysindexkeys	sys.index_columns	Каталог
sysmembers	sys.databases_role_members	Каталог
sysobjects	sys.objects	Каталог
syspermissions	sys.database_permissions, sys.server_permissions	Каталог
sysprotects	sys.database_permissions, sys.server_permissions	Каталог
sysreferences	sys.foreign_keys	Каталог
systypes	sys.types	Каталог
sysusers	sys.database_principals	Каталог

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

Для просмотра сведений о параметрах конфигурации SQL Server и выполнения обще­го администрирования SQL Server 2005 можно использовать системные хранимые процедуры. SQL Server 2005 предоставляет две основные категории системных хра­нимых процедур:

· для администраторов;

· для реализации функциональности интерфейсов программирования приложений (APIs, application programming interfaces) баз данных.

Естественно, вы будете работать с системными хранимыми процедурами, пред­назначенными для администрирования, а не с теми, которые реализуют функции интерфейсов программирования приложений баз данных. Системные хранимые про­цедуры написаны на языке Transact-SQL. Большинство из них возвращают значение 0 в случае успешного завершения и значение, отличное от 0, — при ошибке. Например, хранимая процедура sp_dboption предназначена для управления параметрами конфи­гурации БД SQL Server (кроме БД master и tempdb). При использовании хранимой процедуры sp_dboption для установки значения какого-либо параметра конфигура­ции БД код возврата 0 обозначает, что параметр был установлен, как и ожидалось. Код возврата 1 — что хранимая процедура отработала с ошибкой и параметр не был установлен. Следующий пример переводит БД Personnel в автономный режим, если нет подключенных пользователей:

USE master;

GO

EXEC sp_dboption 'Personnel', 'offline', 'TRUE';

GO

Если хранимая процедура возвращает 0, это означает, что база данных была успеш­но переведена в автономный режим. Возвращаемое значение 1 показывает, что при переводе БД в автономный режим произошла ошибка, то есть база данных осталась в оперативном режиме. Больше информации, касающейся использования хранимых процедур, будет дано дальше, в разделе «Настройка SQL Server с помощью хранимых процедур».

В табл. 4-3 представлен перечень хранимых процедур, применяемых для админи­стрирования. Элементы перечня организованы по компонентам, для работы с кото­рыми предназначена каждая хранимая процедура.

Табл. 4-3. Основные системные хранимые процедуры

Компонент	Описание	Связанные системные хранимые процедуры
Служба каталогов Active Directory	Регистрируют экземпляры SQL Server и БД SQL Server в службе каталогов Active Directory	sp_ActiveDirectory_Obj, sp_ActiveDirectory_SCP
Каталог	Реализуют функции словаря данных ODBC	sp_column_privileges, sp_columns, sp_databases, sp_fkeys, sp_pkeys, sp_server_info, sp_special_columns, sp_sproc_columns, sp_statistics, sp_stored_procedures, sp_table_privileges, sp_tables
Курсоры	Реализуют функциональность переменных курсора	sp_cursor_list, sp_describe_cursor, sp_describe_cursor_columns, sp_describe_cursor_tables
Ядро БД	Обслуживают экземпляры SQL Server и выполняют основные задачи администрирования	sp_add_data_file_recover_suspect_db, sp_add_log_file_recover_suspect_db, sp_addextendedproc, sp_addextendedproperty, sp_addmessage, sp_addtype, sp_addumpdevice, sp_altermessage, sp_attach_db, sp_attach_single_file_db, sp_autostats, sp_bindefault, sp_bindrule, sp_bindsession, sp_certify_removable, sp_configure, sp_create_removable, sp_createstats, sp_cycle_errorlog, sp_datatype_info, sp_dbcmptlevel, sp_dboption, sp_dbremove, sp_delete_backuphistory, sp_depends, sp_detach_db, sp_dropdevice, sp_dropextendedproc, sp_dropextendedproperty, sp_dropmessage, sp_droptype, sp_executesql, sp_getapplock, sp_getbindtoken, sp_help sp_helpconstraint, sp_helpdb, sp_helpdevice, sp_helpextendedproc, sp_helpfile, sp_helpfilegroup, sp_helpindex, sp_helplanguage, sp_helpserver, sp_helpsort, sp_helpstats, sp_helptext, sp_helptrigger, sp_indexoption, sp_invalidate_textptr, sp_lock, sp_monitor, sp_procoption, sp_recompile, sp_refreshview, sp_releaseapplock, sp_rename, sp_renamedb, sp_resetstatus, sp_serveroption, sp_setnetname, sp_settriggerorder, sp_spaceused, sp_tableoption, sp_unbindefault, sp_unbindrule, sp_updateextendedproperty, sp_updatestats, sp_validname, sp_who
Почтовый компонент Database Mail	Выполняют операции с электронной почтой из SQL Server	sp_send_dbmail, sysmail_add_account_sp, sysmail_add_principalprofile_sp, sysmail_add_profile_sp, sysmail_add_profileaccount_sp, sysmail_configure_sp, sysmail_delete_account_sp, sysmail_delete_principalprofile_sp, sysmail_delete_profile_sp, sysmail_delete_profileaccount_sp, sysmail_help_account_sp, sysmail_help_configure_sp, sysmail_help_principalprofile_sp, sysmail_help_profile_sp, sysmail_help_profileaccount_sp, sysmail_start_sp, sysmail_stop_sp, sysmail_update_account_sp, sysmail_update_principalprofile_sp, sysmail_update_profile_sp, sysmail_update_profileaccount_sp
План обслуживания БД	Настраивают планы обслуживания БД, управляют ими, а также выполняют относящиеся к ним задачи	sp_add_maintenance_plan, sp_add_maintenance_plan_db, sp_add_maintenance_plan_job, sp_delete_maintenance_plan, sp_delete_maintenance_plan_db, sp_delete_maintenance_plan_job, sp_help_maintenance_plan
Распределенные запросы	Реализуют распределенные запросы и управляют ими	sp_addlinkedserver, sp_addlinkedsrvlogin, sp_catalogs, sp_column_privileges_ex, sp_columns_ex, sp_droplinkedsrvlogin, sp_foreignkeys, sp_indexes, sp_linkedservers, sp_primarykeys, sp_serveroption, sp_table_privileges_ex, sp_tables_ex, sp_testlinkedserver
Полнотекстовый поиск	Реализуют полнотекстовые индексы и запросы к ним	sp_fulltext_catalog, sp_fulltext_column, sp_fulltext_database, sp_fulltext_service, sp_fulltext_table, sp_help_fulltext_catalogs, sp_help_fulltext_catalogs_cursor, sp_help_fulltext_columns, sp_help_fulltext_columns_cursor, sp_help_fulltext_tables, sp_help_fulltext_tables_cursor
Основные расширенные хранимые процедуры	Предоставляют интерфейс для доступа из SQL Server к внешним программам, в основном для обслуживания сервера	xp_cmdshell, xp_enumgroups, xp_findnextmsg, xp_grantlogin, xp_logevent, xp_loginconfig, xp_logininfo, xp_msver, xp_revokelogin, xp_sprintf, xp_sqlmaint, xp_sscanf
Передача журналов	Реализуют функции передачи журналов, а также позволяют контролировать ее параметры конфигурации и устанавливать их	sp_add_log_shipping_alert_job, sp_add_log_shipping_primary_database, sp_add_log_shipping_primary_secondary, sp_add_log_shipping_secondary_database, sp_add_log_shipping_secondary_primary, sp_change_log_shipping_primary_database, sp_change_log_shipping_secondary_database, sp_change_log_shipping_secondary_primary, sp_cleanup_log_shipping_history, sp_delete_log_shipping_alert_job, sp_delete_log_shipping_primary_database, sp_delete_log_shipping_primary_secondary, sp_delete_log_shipping_secondary_database, sp_delete_log_shipping_secondary_primary, sp_help_log_shipping_alert_job, sp_help_log_shipping_monitor_primary, sp_help_log_shipping_monitor_secondary, sp_help_log_shipping_primary_database, sp_help_log_shipping_primary_secondary, sp_help_log_shipping_secondary_database, sp_help_log_shipping_secondary_primary, sp_refresh_log_shipping_monitor, sp_resolve_logins
Notification Services (Службы уведомлений)	Управляют Notification Services (Службы уведомлений), а также позволяют отлаживать их работу и устранять неполадки	NSAdministrationHistory, NSDiagnosticDeliveryChannel, NSDiagnosticEventClass, NSDiagnosticEventProvider, NSDiagnosticFailedNotifications, NSDiagnosticNotificationClass, NSDiagnosticSubscriptionClass, NSEventBatchDetails, NSEventBeginBatchevent_class_name, NSEventFlushBatchevent_class_name, NSEventSubmitBatchevent_class_name, NSEventWriteevent_class_name, NSExecuteRuleFiring, NSNotificationBatchDetails, NSNotificationBatchList, NSPrepareRuleFiring, NSQuantumDetails, NSQuantumExecutionTime, NSQuantumFailures, NSQuantumList, NSQuantumPerformance, NSQuantumSkipped, NSScheduledSubscriptionDetails, NSScheduledSubscriptionList, NSSetQuantumClock, NSSetQuantumClockDate, NSSnapshotApplications, NSSnapshotDeliveryChannels, NSSnapshotEvents, NSSnapshotProviders, NSSnapshotSubscriptions, NSSubscriptionConditionInformation, NSVacuum
Автоматизация OLE	Создают объекты автоматизации OLE и управляютими	sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OAMethod, sp_OASetProperty, sp_OAStop
Система безопасности	Управляют системой безопасности сервера и БД	sp_addalias, sp_addapprole, sp_addgroup, sp_addlinkedsrvlogin, sp_addlogin, sp_addremotelogin, sp_addrole, sp_addrolemember, sp_addserver, sp_addsrvrolemember, sp_adduser, sp_approlepassword, sp_change_users_login, sp_changedbowner, sp_changegroup, sp_changeobjectowner, sp_dbfixedrolepermission, sp_defaultdb, sp_defaultlanguage, sp_denylogin, sp_dropalias, sp_dropapprole, sp_dropgroup, sp_droplinkedsrvlogin, sp_droplogin, sp_dropremotelogin, sp_droprolemember, sp_dropserver, sp_dropsrvrolemember, sp_dropuser, sp_grantdbaccess, sp_grantlogin, sp_helpdbfixedrole, sp_helpgroup, sp_helplinkedsrvlogin, sp_helplogins, sp_helpntgroup, sp_helpremotelogin, sp_helprole, sp_helprolemember, sp_helprotect, sp_helpsrvrole, sp_helpsrvrolemember, sp_helpuser, sp_MShasdbaccess, sp_password, sp_remoteoption, sp_revokedbaccess, sp_revokelogin, sp_setapprole, sp_srvrolepermission, sp_validatelogins
Почтовая служба SQL Mail	Выполняют операции с электронной почтой из SQL Server (в SQL Server 2005 использование компо- нента Database Mail является предпочтительным)	sp_processmail, xp_deletemail, xp_findnextmsg, xp_readmail, xp_sendmail, xp_startmail, xp_stopmail
Утилита SQL Server Profiler	Используются утилитой SQL Profiler для мониторинга производительности и активности	sp_trace_create, sp_trace_generateevent, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus
Служба SQL Server Agent	Управляют запланирован- ными оповещениями и другой деятельностью SQL Agent	sp_add_alert, sp_add_category, sp_add_job, sp_add_jobschedule, sp_add_jobserver, sp_add_jobstep, sp_add_notification, sp_add_operator, sp_add_proxy, sp_add_schedule, sp_add_targetservergroup, sp_add_targetsvrgrp_member, sp_apply_job_to_targets, sp_attach_schedule, sp_cycle_agent_errorlog, sp_cycle_errorlog, sp_delete_alert, sp_delete_category, sp_delete_job, sp_delete_jobschedule, sp_delete_jobserver, sp_delete_jobstep, sp_delete_jobsteplog, sp_delete_notification, sp_delete_operator, sp_delete_proxy, sp_delete_schedule, sp_delete_targetserver, sp_delete_targetservergroup, sp_delete_targetsvrgrp_member, sp_detach_schedule, sp_enum_login_for_proxy, sp_enum_proxy_for_subsystem, sp_enum_sqlagent_subsystems, sp_grant_login_to_proxy
Служба SQL Server Agent	Управляют запланирован- ными оповещениями и другой деятельностью SQL Agent	sp_grant_proxy_to_subsystem, sp_help_alert, sp_help_category, sp_help_downloadlist, sp_help_job, sp_help_jobactivity, sp_help_jobcount, sp_help_jobhistory, sp_help_jobs_in_schedule, sp_help_jobschedule, sp_help_jobserver, sp_help_jobstep, sp_help_jobsteplog, sp_help_notification, sp_help_operator, sp_help_proxy, sp_help_schedule, sp_help_targetserver, sp_help_targetservergroup, sp_manage_jobs_by_login, sp_msx_defect, sp_msx_enlist, sp_msx_get_account, sp_msx_set_account, sp_notify_operator, sp_post_msx_operation, sp_purge_jobhistory, sp_remove_job_from_targets, sp_resync_targetserver, sp_revoke_login_form_proxy, sp_revoke_proxy_from_subsystem, sp_start_job, sp_stop_job, sp_update_alert, sp_update_category, sp_update_job, sp_update_jobschedule, sp_update_jobstep, sp_update_notification, sp_update_operator, sp_update_proxy, sp_update_schedule, sp_update_targetservergroup
Документы XML	Управляют текстом в формате XML	sp_xml_preparedocument, sp_xml_removedocument

Дата добавления: 2014-01-04