Хранимые процедуры ядра СУБД (Transact-SQL) часть 2

Административные скрипты MS SQL > Хранимые процедуры ядра СУБД (Transact-SQL) часть 2
11.04.2013 12:16:41



Статья:

sp_who Предоставляет сведения о текущих пользователях, сеансах и процессах в экземпляре компонента Microsoft SQL Server Database Engine. Данные могут быть отфильтрованы, чтобы возвращать только те процессы, которые не простаивают, принадлежат конкретному пользователю или принадлежат определенному сеансу.

sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
 

Процедура sp_who возвращает результирующий набор со следующими данными.

Столбец

Тип данных

Описание

spid

smallint

Идентификатор сеанса.

ecid

smallint

Идентификатор контекста выполнения заданного потока, связанный с определенным идентификатором сеанса.

ECID = {0, 1, 2, 3, ...n}, где 0 всегда представляет основной или родительский поток, а {1, 2, 3, ...n} представляет подпроцессы.

status

nchar(30)

Состояние процесса. Возможны следующие значения.

dormant. SQL Server сбрасывает сеанс.

running. В сеансе выполняются один или несколько пакетов. Если включен режим MARS, в сеансе может выполняться несколько пакетов. Дополнительные сведения см. в разделе Использование режима MARS.

background. В сеансе выполняется фоновая задача, например обнаружение взаимоблокировок.

rollback. В сеансе выполняется откат транзакций.

pending. В сеансе ожидается освобождение потока исполнителя.

runnable. Задачи сеанса находятся в очереди исполнителей планировщика, ожидая времени такта.

spinloop. Задачи сеанса ожидают освобождения взаимоблокировки.

suspended. Сеанс ожидает завершения события, например операции ввода-вывода.

loginame

nchar(128)

Имя входа, связанное со специфическим процессом.

hostname

nchar(128)

Имя узла или компьютера для каждого процесса.

blk

char(5)

Идентификатор сеанса для блокирующего процесса, если такой существует. В противном случае значение этого столбца — 0.

Если транзакция, связанная с данным идентификатором сеанса, заблокирована потерянной распределенной транзакцией, этот столбец возвратит -2 для блокирующей потерянной транзакции.

dbname

nchar(128)

База данных, используемая процессом.

cmd

nchar(16)

Команда компонента Database Engine (инструкция Transact-SQL, внутренний процесс компонента Database Engine и так далее), выполняющаяся для процесса.

request_id

int

Идентификатор для запросов, запущенных в определенном сеансе.

При параллельной обработке подпроцессы создаются для определенного идентификатора сеанса. Главный поток обозначается как spid = <xxx> и ecid =0. Другие подпроцессы имеют одинаковый spid = <xxx>, но с ecid > 0.

 

 

sp_validname Проверяет на правильность имена идентификаторов SQL Server. Все недвоичные и ненулевые данные, включая данные в кодировке Юникод, которые могут быть сохранены, используя типы данных nchar,nvarchar или ntext, принимаются как допустимые символы для имен идентификаторов.

sp_validname [@name =] 'name' 
     [, [@raise_error =] raise_error]
 
sp_unprepare Отменяет план выполнения, созданный хранимой процедурой sp_prepare. Процедура sp_unprepare инициируется при указании ID = 15 в пакете потока табличных данных (TDS).
sp_unprepare handle
 
sp_updatestats Запускает инструкцию UPDATE STATISTICS для всех пользовательских и внутренних таблиц в текущей базе данных.
 sp_updatestats [ [ @resample = ] 'resample']
 
 
sp_updateextendedproperty Обновляет значение существующего расширенного свойства.
sp_updateextendedproperty
    [ @name = ]{ 'property_name' } 
    [ , [ @value = ]{ 'value' }
        [, [ @level0type = ]{ 'level0_object_type' }
                  , [ @level0name = ]{ 'level0_object_name' }
              [, [ @level1type = ]{ 'level1_object_type' }
                              , [ @level1name = ]{ 'level1_object_name' }
                     [, [ @level2type = ]{ 'level2_object_type' }
                                            , [ @level2name = ]{ 'level2_object_name' }
                     ]
              ]
        ]
    ]
 
 
sp_unbindrule Отменяет привязку правила к столбцу или псевдониму типа данных в текущей базе данных.
sp_unbindrule [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ]
 
 
 
sp_unbindefault Отменяет привязку или удаляет значение по умолчанию из столбца или псевдонима типа данных в текущей базе данных.
sp_unbindefault [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ]
 
sp_tableoption Устанавливает значения параметров для определяемых пользователем таблиц. Функция sp_tableoption может использоваться для контроля внутристрокового поведения таблиц со столбцами varchar(max),nvarchar(max), varbinary(max), xml, text, ntext, image, а также с большими пользовательскими столбцами.
sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          ,[ @OptionValue =] 'value'
 
sp_spaceused Выводит количество строк, зарезервированное место на диске и место на диске, которое используется таблицей, индексированным представлением или очередью компонента Service Broker в текущей базе данных, либо выводит место на диске, зарезервированное и используемое всей базой данных.
sp_spaceused [[ @objname = ] 'objname' ] 
[,[ @updateusage = ] 'updateusage' ]

Если аргумент objname не указан, возвращаются следующие результирующие наборы для предоставления сведений о текущем размере базы данных.

Имя столбца

Тип данных

Описание

database_name

nvarchar(128)

Имя текущей базы данных.

database_size

varchar(18)

Размер текущей базы данных в мегабайтах. Значениеdatabase_size включает как файлы данных, так и файлы журналов.

unallocated space

varchar(18)

Место в базе данных, не зарезервированное для объектов базы данных.

Имя столбца

Тип данных

Описание

reserved

varchar(18)

Общий объем пространства, выделенный объектам в базе данных.

data

varchar(18)

Общий объем пространства, используемый данными.

index_size

varchar(18)

Общий объем пространства, используемый индексами.

unused

varchar(18)

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

Если указан аргумент objname, для указанного объекта возвращается следующий результирующий набор.

Имя столбца

Тип данных

Описание

name

nvarchar(128)

Имя объекта, для которого были запрошены сведения об используемом пространстве.

Имя схемы объекта не возвращается. Если требуется имя схемы, для получения эквивалентных сведений о размере используется динамическое административное представлениеsys.dm_db_partition_stats или sys.dm_db_index_physical_stats.

rows

char(11)

Количество существующих строк в таблице. Если объект указан как очередь компонента Service Broker, этот столбец указывает число сообщений в очереди.

reserved

varchar(18)

Общий объем зарезервированного пространства для объекта objname.

data

varchar(18)

Общий объем пространства, используемый данными объекта objname.

index_size

varchar(18)

Общий объем пространства, используемый индексами объекта objname.

unused

varchar(18)

Общий объем пространства, зарезервированный для объекта objname, но пока не используемый.

 
 
sp_settriggerorder Указывает триггеры AFTER, срабатывающие первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке.
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername' 
        , [ @order = ] 'value' 
        , [ @stmttype = ] 'statement_type' 
        [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
 
sp_settriggerorder Указывает триггеры AFTER, срабатывающие первыми или последними. Триггеры AFTER, срабатывающие между первым и последним триггерами, выполняются в неопределенном порядке.
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername' 
        , [ @order = ] 'value' 
        , [ @stmttype = ] 'statement_type' 
        [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
 
sp_setnetname Присваивает сетевым именам удаленных экземпляров SQL Server в представлении каталога sys.serversдействительные сетевые имена их компьютеров. Эта процедура может быть использована для разрешения выполнения вызовов удаленных хранимых процедур тем компьютерам, сетевые имена которых содержат неверные идентификаторы SQL Server.
sp_setnetname
@server = 'server', 
          @netname = 'network_name'
 
sp_serveroption Устанавливает параметры сервера для удаленных и связанных серверов.
sp_serveroption [@server = ] 'server' 
      ,[@optname = ] 'option_name'     
      ,[@optvalue = ] 'option_value' ;
 
sp_resetstatus Сбрасывает состояние SUSPECT для базы данных.
sp_resetstatus [ @dbname = ] 'database'
 
sp_renamedb Изменяет имя базы данных.
sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'
 
sp_rename Изменяет имя пользовательского объекта в текущей базе данных. Этот объект может быть таблицей, индексом, столбцом, псевдонимом типа данных или определяемым пользователем типом среды CLR Microsoft .NET Framework.
sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' 
    [ , [ @objtype = ] 'object_type' ] 
 
sp_releaseapplock Снимает блокировку ресурса приложения.
sp_releaseapplock [ @Resource = ] 'resource_name' 
     [ , [ @LockOwner = ] 'lock_owner' ]
     [ , [ @DbPrincipal = ] 'database_principal' ]
[ ; ]

В следующем примере освобождается блокировка ресурса Form1, связанная с текущей транзакцией в базе данных AdventureWorks2008R2.

USE AdventureWorks2008R2;
GO
EXEC sp_getapplock @DbPrincipal = 'dbo', @Resource = 'Form1', 
     @LockMode = 'Shared';
EXEC sp_releaseapplock @DbPrincipal = 'dbo', @Resource = 'Form1';
GO
 
sp_refreshview  Обновляет метаданные для заданного, не привязанного к схеме, представления. Постоянные метаданные представления могут устареть вследствие изменения базовых объектов, от которых зависит представление.
sp_refreshview [ @viewname = ] 'viewname' 
 
 
sp_recompile Вызывает повторную компиляцию хранимых процедур и триггеров при следующем запуске. Для этого из кэша процедур удаляется существующий план, в результате чего при следующем запуске процедуры или триггера создается новый план. В коллекции Приложение SQL Server Profiler вместо события SP:Recompile в журнал записывается событие SP:CacheInsert.
sp_recompile [ @objname= ] 'object'
 
sp_procoption Устанавливает автоматическое выполнение хранимой процедуры. Эта хранимая процедура будет выполняться каждый раз при запуске сервера SQL Server.
sp_procoption [ @ProcName = ] 'procedure' 
    , [ @OptionName = ] 'option' 
    , [ @OptionValue = ] 'value' 
 
sp_prepexecrpc Подготавливает и выполняет вызов параметризованной хранимой процедурой, заданной с помощью идентификатора RPC. Для вызова процедуры sp_prepexecrpc необходимо задать ID = 14 в пакете потока табличных данных (TDS).
sp_prepexecrpc handle, OUTPUT RPCCall
        [ , bound_param ] [ ,...n ] ]
 
sp_prepexec Подготавливает и выполняет параметризованную инструкцию Transact-SQL. Процедура sp_prepexec объединяет в себе процедуры sp_prepare и sp_execute. Вызывается с ID =13 в пакете потока табличных данных (TDS).
sp_prepexec handle OUTPUT, params , stmt
        [ , bound param ] [ ,...n ] ]
sp_prepare Подготавливает параметризованную инструкцию Transact-SQL и возвращает дескриптор инструкции для выполнения. Процедура sp_prepare вызывается указанием ID = 11 в пакете потока табличных данных (TDS).
sp_prepare handle OUTPUT, params, stmt, options 
 
sp_monitor Выводит статистику о Microsoft SQL Server.
 
sp_lock Сообщает сведения о блокировках.
sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]
[ ; ]

Результирующий набор процедуры sp_lock содержит по одной строке на каждую блокировку процессов, заданных аргументами @spid1 и @spid2. Если ни один из аргументов @spid1 и @spid2 не задан, результирующий набор содержит блокировки для всех процессов, которые активны в данный момент в экземпляре компонента Database Engine.

Имя столбца

Тип данных

Описание

spid

smallint

Числовой идентификатор сеанса компонента Database Engine для процесса, запрашивающего блокировку.

dbid

smallint

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

ObjId

int

Числовой идентификатор объекта, на который удерживается блокировка. Для идентификации объекта можно использовать функцию OBJECT_NAME() в связанной базе данных. Значение 99 является особым, и означает блокировку на одной из системных страниц, используемых для записи распределенных страниц в базе данных.

IndId

smallint

Числовой идентификатор индекса, для которого удерживается блокировка.

Type

nchar(4)

Типы блокировки:

RID = Блокировка на одну строку в таблице, задаваемой идентификатором строки (RID — row ID);

KEY = Блокировка внутри индекса, которая защищает диапазон ключей в сериализуемых транзакциях;

PAG = Блокировка данных или индексной страницы;

EXT = Блокировка на экстент.

TAB = Блокировка на целую таблицу, включая все данные и индексы;

DB = Блокировка на базу данных;

FIL = Блокировка на файл базы данных;

APP = Блокировка на ресурс приложения;

MD = Блокировка на метаданные или данные о каталоге;

HBT = Блокировка на кучу или индекс сбалансированного дерева (B-Tree). Эти сведения неполные в SQL Server.

AU = Блокировка на единицу распределения (allocation unit). Эти сведения неполные в SQL Server.

Resource

nchar(32)

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

Type Значение: Resource Значение

RID: Идентификатор в формате fileid:pagenumber:rid, где fileid определяет файл, содержащий страницу, pagenumber определяет страницу, содержащую строку, а rid определяет заданную строку на странице. fileid соответствует столбцу file_id в представлении каталога sys.database_files.

KEY: шестнадцатеричное число, используемое компонентом Database Engine для внутренних целей.

PAG: Число в формате fileid:pagenumber, где fileid определяет файл, содержащий страницу, а pagenumber определяет страницу.

EXT: Число, определяющее первую страницу в экстенте. Число в формате fileid:pagenumber.

TAB: Сведения не предоставляются, так как таблица уже определена в столбце ObjId.

DB: Сведения не предоставляются, так как база данных уже определена в столбце dbid.

FIL: Идентификатор файла, который соответствует столбцу file_id в представлении каталога sys.database_files.

APP: Уникальный идентификатор блокируемого ресурса приложения. В формате DbPrincipleId:<от 2 до 16 первых символов строки ресурса><значение хэша>.

MD: зависит от типа ресурса. Дополнительные сведения см. в описании столбца resource_description в разделе sys.dm_tran_locks (Transact-SQL).

HBT: Сведения не предоставлены. Вместо этого необходимо использовать динамическое административное представление каталогаsys.dm_tran_locks.

AU: Сведения не предоставлены. Вместо этого необходимо использовать динамическое административное представление каталогаsys.dm_tran_locks.

Mode

nvarchar(8)

Запрашиваемый режим блокировки. Может быть:

NULL = Блокировки нет. Служит как местозаполнитель (placeholder);

Sch-S = Блокировка стабильности схемы. Гарантирует, что элемент схемы, такой как таблица или индекс, не будет удален до тех пор, пока сеанс связи удерживает блокировку стабильности схемы на данный элемент схемы;

Sch-М = Блокировка изменения схемы. Должен поддерживаться любым сеансом связи, во время которого предполагается изменить схему данного ресурса. Гарантирует, что другие сеансы не имеют ссылок на обозначенный объект;

S = Коллективная блокировка. Удерживающему сеансу предоставлен коллективный доступ к ресурсу;

U = Блокировка обновления. Указывает блокировку обновления, полученную на ресурсы, которые со временем могут быть обновлены. Используется для предотвращения общей формы взаимоблокировки, которая возникает, когда множество сеансов блокируют ресурсы для потенциального обновления в последующее время;

X = Монопольная блокировка. Удерживающему сеансу предоставлен исключительный доступ к ресурсу;

IS = Блокировка с намерением коллективного доступа. Указывает намерение поместить S блокировки на некоторые подчиненные ресурсы в иерархии блокировок;

IU = Блокировка с намерением обновления. Указывает намерение поместить U блокировки на некоторые подчиненные ресурсы в иерархии блокировок;

IX = Блокировка с намерением монопольного доступа. Указывает намерение поместить X блокировки на некоторые подчиненные ресурсы в иерархии блокировок;

SIU = Коллективная блокировка с намерением обновления. Указывает коллективный доступ к ресурсу с намерением получения блокировок обновления на подчиненные ресурсы в иерархии блокировок;

SIX = Коллективная блокировка с намерением монопольного доступа. Указывает коллективный доступ к ресурсу с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок;

UIX = Блокировка обновления с намерением монопольного доступа. Указывает блокировку обновления ресурса с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок;

BU = Блокировка массового обновления. Используется для массовых операций;

RangeS_S = Блокировка разделяемого диапазона ключей и разделяемых ресурсов. Указывает на упорядоченный просмотр диапазона;

RangeS_U = Блокировка разделяемого диапазона ключей и обновляемых ресурсов. Указывает на упорядоченный просмотр обновлений;

RangeI_N = Блокировка вставляемого диапазона ключей и NULL-ресурсов. Используется для проверки диапазонов, перед тем как вставить новый ключ в индекс;

RangeI_S = блокировка преобразования диапазона ключей. Создается перекрытием блокировок RangeI_N и S;

RangeI_U = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и U;

RangeI_X = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и X;

RangeX_S = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и RangeS_S;

RangeX_U = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и RangeS_U.

RangeX_X = Блокировка монопольного диапазона ключей и монопольных ресурсов. Блокировка преобразования, используемая при обновлении ключа в диапазоне.

Status

nvarchar(5)

Состояние запроса блокировки:

CNVRT: Блокировка, преобразованная в другом режиме, в ситуации блокирования другого процесса, удерживающего блокировку в конфликтном режиме.

GRANT: Блокировка получена.

WAIT: Блокировка занята другим процессом, удерживающим блокировку в конфликтном режиме.

 
 
sp_invalidate_textptr Делает недействительным заданный указатель текста строки в транзакции или все подобные указатели. Процедуру sp_invalidate_textptr можно использовать только в указателе текста строки. Эти указатели находятся в таблицах с активированным параметром text in row.
sp_invalidate_textptr [ [ @TextPtrValue = ] textptr_value ]
 
sp_indexoption Устанавливает блокирующие значения параметров для определенных пользователем кластеризованных и некластеризованных индексов или таблиц, не имеющих кластеризованного индекса.
sp_indexoption [ @IndexNamePattern = ] 'table_or_index_name' 
        , [ @OptionName = ] 'option_name' 
        , [ @OptionValue = ] 'value'
 
sp_helptrigger Возвращает тип или типы триггеров DML, определенных в указанной таблице для текущей базы данных. Процедуру sp_helptrigger нельзя использовать с триггерами DDL. Вместо этого следует запрашивать представление каталога системные хранимые процедуры.
sp_helptrigger [ @tabname = ] 'table' 
     [ , [ @triggertype = ] 'type' ]
 
sp_helptext Отображает определение определенного пользователем правила, по умолчанию нешифрованной хранимой процедуры на языке Transact-SQL, определенной пользователем функции на языке Transact-SQL, триггера, вычисляемого столбца, ограничения CHECK, вида или системного объекта, такого как системная хранимая процедура.
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]
 
sp_helpstats Возвращает статистические сведения о столбцах и индексах указанной таблицы.
sp_helpstats[ @objname = ] 'object_name' 
     [ , [ @results = ] 'value' ]
 
sp_helpsort Показывает порядок сортировки и кодировку для экземпляра SQL Server 2008.
 
sp_helpserver Возвращает сведения об определенном удаленном сервере, сервере репликации либо обо всех серверах обоих типов. Выдает имя сервера, сетевое имя сервера, состояние репликации сервера, его идентификационный номер, а также имя параметров сортировки. Кроме того, она возвращает значения тайм-аута для подключения к связанным серверам или для выполнения запросов к ним.
sp_helpserver [ [ @server = ] 'server' ] 
  [ , [ @optname = ] 'option' ] 
  [ , [ @show_topology = ] 'show_topology' ]
 
sp_helplanguage Выдает отчет о конкретном альтернативном языке или обо всех языках.
sp_helplanguage [ [ @language = ] 'language' ]
 
sp_helpindex Сообщает данные об индексах в таблице или представлении.
sp_helpindex [ @objname = ] 'name'
 
sp_helpfilegroup Возвращает имена и атрибуты файловых групп, связанных с текущей базой данных.
sp_helpfilegroup [ [ @filegroupname = ] 'name' ]
 
sp_helpfile Возвращает физические имена и атрибуты файлов, связанных с текущей базой данных. Используйте эту хранимую процедуру для определения имен файлов, чтобы присоединять или отсоединять их от сервера.
sp_helpfile [ [ @filename= ] 'name' ]
 
sp_helpextendedproc Сообщает сведения о расширенных хранимых процедурах, определенных в настоящий момент, и имя динамически подключаемой библиотеки (DLL), которой принадлежит эта процедура (функция).
sp_helpextendedproc [ [@funcname = ] 'procedure' ]
 
sp_helpdevice Возвращает сведения об устройствах резервного копирования Microsoft® SQL Server™.
sp_helpdevice [ [ @devname = ] 'name' ]
 
sp_helpdb Сообщает информацию об указанной базе данных или всех базах данных.
sp_helpdb [ [ @dbname= ] 'name' ]
 
sp_helpconstraint Возвращает список всех типов ограничений, их пользовательские или предоставляемые системой имена, столбцы, на которых они определены, и выражения, определяющие ограничения (только для ограничений DEFAULT и CHECK).
sp_helpconstraint [ @objname = ] 'table' 
     [ , [ @nomsg = ] 'no_message' ] 
 
sp_help Возвращает сведения об объекте базы данных (любом объекте, присутствующем в представлении совместимости sys.sysobjects), обычном или определяемом пользователем типе данных.
sp_help [ [ @objname = ] 'name' ]
 
sp_getbindtoken Возвращает уникальный идентификатор для транзакции. Этот уникальный идентификатор является строкой, используемой для привязки сеансов при помощи процедуры sp_bindsession.
sp_getbindtoken [@out_token =] 'return_value' OUTPUT 
 
sp_getapplock Размещает блокировку на ресурсе приложения.
sp_getapplock [ @Resource = ] 'resource_name' ,
     [ @LockMode = ] 'lock_mode' 
     [ , [ @LockOwner = ] 'lock_owner' ] 
     [ , [ @LockTimeout = ] 'value' ]
     [ , [ @DbPrincipal = ] 'database_principal' ]
[ ; ]
 
sp_executesql Выполняет инструкцию Transact-SQL или пакет инструкций, которые могут выполняться много раз или создаваться динамически. Инструкция Transact-SQL или пакет инструкций могут содержать параметры.
sp_executesql [ @statement = ] statement
[ 
    { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
     { , [ @param1 = ] 'value1' [ ,...n ] }
]
 
sp_execute Выполняет подготовленную инструкцию Transact-SQL, используя указанное значение handle и значение необязательного параметра. sp_execute вызывается путем присвоения идентификатору в пакете потока табличных данных (TDS) значения 12.
sp_execute handle OUTPUT
                          [ ,bound_param ] [ ,...n ] ]