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

Question

Я привык делать это:

use MyDb
go
create type table_list as table (name sysname)
go
create proc rebuild_indexes
    @ls table_list readonly
as
  [...]

но теперь я хочу создать proc в master и использовать его в других базах данных:

use master
go
create type table_list as table (name sysname)
go
create proc sp_rebuild_indexes
    @ls table_list readonly
as
  [...]

... он прекрасно работает, но я не могу его использовать!

use MyDb
go
declare @ls table_list

выходы:

Сообщение 2715, уровень 16, состояние 3, строка 1, столбец, параметр или переменная #1: невозможно найти тип данных table_list. Параметр или переменная '@ls' имеют недопустимый тип данных.

поэтому я попытался создать синоним, который не жалуется при создании, но не помогает:

create synonym table_list for master.dbo.table_list

мысли кто-нибудь?

Answer 1

Типы таблиц, объявленные в разных базах данных, даже если они имеют одинаковое имя и структуру, не рассматриваются SQL Server как одинаковые:

create database DB1
go
use DB1
go
create type TT1 as table (ID int not null)
go
create procedure Echo
    @T TT1 readonly
as
    select * from @T
go
create database DB2
go
use DB2
go
create type TT1 as table (ID int not null)
go
declare @T TT1
insert into @T(ID) values (1)
exec DB1..Echo @T

Результат:

(1 row(s) affected)
Msg 206, Level 16, State 2, Procedure Echo, Line 0
Operand type clash: TT1 is incompatible with TT1

Насколько я знаю, нет способа объявить переменную в базе данных, используя определение типа таблицы из другой базы данных. (например, везде, где вы видите, пользовательский тип таблицы может быть использован, он может быть назван только как <table type> или же <schema>.<table type>, 3 или 4 части названия не допускаются)

(Вышесказанное верно для 2008 и 2012 годов; очевидно, что будущие версии могут что-то сделать для решения этой проблемы)

---

В качестве обходного пути, вы можете сделать это "бедным человеком" - заставить вашу основную хранимую процедуру работать с временной таблицей, а не с определенным пользователем типом таблицы:

use Master
go
create procedure sp_Echo
as
    select DB_NAME(),* from #t
go
create database DB1
go
use DB1
go
create table #t (ID int not null)
insert into #t (ID) values (1),(2)
exec sp_Echo

Результаты:

------------- -----------
DB1           1
DB1           2

Answer 2

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

Возможно, вам придется создать UDT в каждой базе данных, но оставить sProc в master.

Есть ли конкретная причина, по которой вы используете соглашение об именах sp_?

Пита