При возникновении ошибок (да и не только), разработчикам хочется видеть стек вызова процедур.
И разработчики баз данных - не исключение.
Но к сожалению, T-SQL в частности и MSSQL Server вцелом не предоставляют нам такой счастливой возможности.
Оказывается все не так плохо...
Межно и самим сделать. ;)
Ограничение нижеследующего решения (касается текста процедур, обращения к которым хочется видеть в стеке):
- процедуры будут модифицированы (возможны проблемы с ПО "сторонних разрабитчиков", которые требуют невмешательства)
- процедуры должны создаваться командой create procedure, начинающейся с первого символа любой строки
- слова create & procedure разделяются строго одним пробелом, после procedure также идет строго один пробел.
- ключевое слово as также должно начинаться с первого символа строки, других символов в строке быть не должно.
Впрочем, данные ограничения связаны с нежеланием (надеюсь, понятным) применять в данном примере сложный парсинг T-SQL.
Ну и конечно, никто не мешает поменять данный код с учетом применяемых правил и традиций форматирования своего кода.
Использование:
- Запускаем скрипт
- Выполняем _Context.UpdateProcedures
(в качестве параметра можно передать имя схемы, тогда будут затронуты только процедуры данной схемы) - В процедурах смотрим содержимое стека конструкцией
select * from _Context.Stack()
Обсдуждение решение здесь:
Ну и само решение:
-- Схема
if schema_id('_Context') is null exec('create schema _Context')
go
if object_id('_Context.Push') is not null drop procedure _Context.Push
go
-- Процедура, меняющая контекст
create procedure _Context.Push
@ID int
as
begin
declare
@level int,
@Context varbinary(128)
select
@Context = isnull(context_info(), convert(varbinary(128), replicate(convert(binary(1), 0x00), 128))),
@level = (@@NESTLEVEL - 1) * 4
set @Context = substring(@Context, 1, @level) + convert(binary(4), @ID) + substring(@Context, @level + 5, 128)
set context_info @Context
end
go
-- Опорная таблица с числами для "парсинга" контекста
if object_id('_Context.Numbers') is not null drop table _Context.Numbers
go
create table _Context.Numbers(Number int primary key)
go
-- Заполняем значениями от 1 до 32
insert into _Context.Numbers(Number)
select sv.number
from master.dbo.spt_values sv (nolock)
where sv.[type] = 'P'
and sv.number > 0
and sv.number <= 32
go
if object_id('_Context.Stack') is not null drop function _Context.Stack
go
-- Функция просмотра стека
create function _Context.Stack()
returns table
as
return
(
select
stack_level,
'['+object_schema_name(proc_id)+'].['+object_name(proc_id)+']' as procedure_name
from
(
select
n.Number as stack_level,
convert(int, substring(context_info(), n.Number*4+1, 4)) as proc_id
from _Context.Numbers n (nolock)
where n.Number <= @@NESTLEVEL
) q
)
go
if object_id('_Context.UpdateProcedures') is not null drop procedure _Context.UpdateProcedures
go
-- Процедура, добавляющая изменение стека в уже существующие
create procedure _Context.UpdateProcedures
@SchemaName sysname = null
as
begin
declare
@procid int,
@definition nvarchar(max),
@_definition nvarchar(max),
@CRLF char(2),
@create_pos int,
@as_pos int
set @CRLF = char(13)+char(10)
declare curs cursor local static forward_only for
select
procid,
object_definition(procid)
from
(
select
object_id('['+r.ROUTINE_SCHEMA + '].['+r.ROUTINE_NAME+']') as procid
from INFORMATION_SCHEMA.ROUTINES r
where r.ROUTINE_TYPE='PROCEDURE'
and isnull(@SchemaName, r.ROUTINE_SCHEMA) = r.ROUTINE_SCHEMA
-- Не меняем "себя"
and not(r.ROUTINE_SCHEMA='_Context' and r.ROUTINE_NAME='Push')
) q
open curs
while 1=1
begin
fetch next from curs into @procid, @_definition
if @@FETCH_STATUS <> 0 break
select
@create_pos = 0,
@definition = upper(@_definition)
-- ищем первое вхождение CREATE PROCEDURE
if substring(@definition, 1, 17) = 'CREATE PROCEDURE '
set @create_pos = 1
else
begin
set @create_pos = charindex(@CRLF+'CREATE PROCEDURE ', @definition) + 2
end
if @create_pos > 0
begin
set @as_pos = charindex(@CRLF+'AS'+@CRLF, @definition, @create_pos + 17)
if @as_pos > 0
begin
-- проверяем, нет ли уже за AS exec _Context.Push @@RPOCID
-- если нет - меняем текст процедуры
if substring(@definition, @as_pos+6, 29) <> 'EXEC _CONTEXT.PUSH @@PROCID'+@CRLF
begin
set @_definition = stuff(@_definition, @as_pos+6, 0, 'exec _Context.Push @@PROCID'+@CRLF)
set @_definition = stuff(@_definition, @create_pos, 6, 'alter')
begin try
exec(@_definition)
end try
begin catch
print @_definition
end catch
end
end
end
end
end
go
if object_id('_Context.pLevA') is not null drop procedure _Context.pLevA
go
-- В данной процедуре почему-то хочется видеть стек вызова
create procedure _Context.pLevA
as
begin
select * from _Context.Stack()
select 'level a' as [level]
end
go
-- Процедура, вызывающая ту, в которой хочется видеть стек
if object_id('_Context.pLevB') is not null drop procedure _Context.pLevB
go
create procedure _Context.pLevB
as
exec _Context.pLevA
select 'level b' as [level]
go
if object_id('_Context.pLevC') is not null drop procedure _Context.pLevC
go
-- Процедура, вызывающая процедуру, которая вызывает ту, в которой хочется видеть стек
create procedure _Context.pLevC
as
exec _Context.pLevB
select 'level c' as [level]
go
-- Добавляем Push в существующие процедуры
exec _Context.UpdateProcedures _Context
go
-- Проверяем
exec _Context.pLevC
go