Я не получаю такой вариант, как "ALTER TO", когда нажимаю правой кнопкой мыши на TVP
Как изменить параметр значения таблицы
Ответ 1
Невозможно это сделать. Вы должны бросить/воссоздать. Если у вас есть зависимости от TVP, вы должны:
- создать новый TVP под новым именем
- изменить зависимости для использования (1)
- удалить старый TVP
- воссоздать (1) под оригинальным именем
- изменить зависимости для использования (4)
- drop (1)
Ответ 2
Я нашел сообщение в блоге на sqltreeo.com, в котором есть способ автоматизировать процесс, временно отбрасывая зависимости, а затем повторно создавая их.
Я немного изменил его.
1.Вы должны создать следующую процедуру:
-- Find all referencing objects to user-defined table type in @fullObjectName parameter
-- and generate DROP scripts and CREATE scripts for them
CREATE PROC [dbo].[alterTableType] (@fullObjectName VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
IF (TYPE_ID (@fullObjectName) IS NULL)
BEGIN
RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName)
RETURN
END;
WITH sources
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition
FROM sys.sql_expression_dependencies d
JOIN sys.sql_modules m ON m.object_id = d.referencing_id
JOIN sys.objects o ON o.object_id = m.object_id
WHERE referenced_id = TYPE_ID(@fullObjectName)
)
SELECT 'BEGIN TRANSACTION'
UNION ALL
SELECT
'DROP ' +
CASE OBJECTPROPERTY(referencing_id, 'IsProcedure')
WHEN 1 THEN 'PROC '
ELSE
CASE
WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION '
ELSE ''
END
END
+ SCHEMA_NAME(o.schema_id) + '.' +
+ OBJECT_NAME(m.object_id)
FROM sys.sql_expression_dependencies d
JOIN sys.sql_modules m ON m.object_id = d.referencing_id
JOIN sys.objects o ON o.object_id = m.object_id
WHERE referenced_id = TYPE_ID(@fullObjectName)
UNION ALL
SELECT 'GO'
UNION ALL
SELECT CHAR(13) + CHAR(10) + '---- WRITE HERE SCRIPT TO DROP OLD USER DEFINED TABLE TYPE AND CREATE A NEW ONE ----' + CHAR(13) + CHAR(10)
UNION ALL
SELECT
CASE
WHEN number = RowId THEN DEFINITION
ELSE 'GO'
END
FROM sources s
JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1
UNION ALL
SELECT 'COMMIT'
END
2. Затем вы должны запустить его с именем типа таблицы в качестве входного параметра. Покажите результаты в формате сетки (поскольку текстовый формат может обрезать длинные тексты), выберите всю таблицу результатов и скопируйте ее в новое окно запроса.
Ответ 3
Проблема
Вы не можете удалить пользовательский тип таблицы, если на него ссылается что-либо еще:
Невозможно удалить тип "dbo.MyTableType", так как на него ссылается объект "MyStoredProcedure". Там могут быть другие объекты, которые ссылаются на этот тип.
Было бы неплохо, если бы SSMS предоставил вам список всех других объектов, но если у вас их нет, частично ручной подход может сработать.
Найти использование
Чтобы получить список всех SP, которые используют ваш тип TVP, вы можете запросить sys.sql_expression_dependencies
SELECT OBJECT_NAME(d.referencing_id)
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id = TYPE_ID('MyTableType')
шаги
- Выберите все SP, указанные выше, и выберите
DROP and CREATE to
новое окно - Просмотрите каждое окно и просто выделите/выполните раздел
DROP PROCEDURE
- Теперь вы можете выбрать свой тип и выбрать
DROP and CREATE to
новое окно и внести любые изменения - Вернитесь к списку открытых окон SP и выполните раздел
CREATE