1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| ALTER TRIGGER TR_DB_CHECK_OBJECT_NAME
ON DATABASE
FOR CREATE_TABLE, CREATE_VIEW, CREATE_PROCEDURE, CREATE_FUNCTION, ALTER_TABLE
AS
BEGIN
DECLARE @ddl_event_data xml
, @object_name nvarchar(256)
, @object_type nvarchar(60)
, @new_object_name nvarchar(256)
-- Capture des caractéristiques de l'événement DDL
SET @ddl_event_data = EVENTDATA()
-- Extraction du nom de l'objet
SELECT @object_name = @ddl_event_data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
-- Recherche du type de l'object
SELECT @object_type = type_desc
FROM sys.objects
WHERE name = @object_name
-- Calcul du nouveau nom de l'object, le cas échéant
SELECT @new_object_name = CASE
WHEN @object_type = 'CHECK_CONSTRAINT' AND @object_name NOT LIKE 'CK?_%' ESCAPE '?' THEN 'CK_' + @object_name
WHEN @object_type = 'UNIQUE_CONSTRAINT' AND @object_name NOT LIKE 'UK?_%' ESCAPE '?' THEN 'UK_' + @object_name
WHEN @object_type = 'PRIMARY_KEY_CONSTRAINT' AND @object_name NOT LIKE 'PK?_%' ESCAPE '?' THEN 'PK_' + @object_name
WHEN @object_type = 'FOREIGN_KEY_CONSTRAINT' AND @object_name NOT LIKE 'CK?_%' ESCAPE '?' THEN 'FK_' + @object_name
WHEN @object_type = 'SQL_SCALAR_FUNCTION' AND @object_name NOT LIKE 'F?_%' ESCAPE '?' THEN 'F_' + @object_name
WHEN @object_type = 'SQL_INLINE_TABLE_VALUED_FUNCTION' AND @object_name NOT LIKE 'F?_%' ESCAPE '?' THEN 'F_' + @object_name
WHEN @object_type = 'SQL_TABLE_VALUED_FUNCTION' AND @object_name NOT LIKE 'F?_%' ESCAPE '?' THEN 'F_' + @object_name
WHEN @object_type = 'SQL_STORED_PROCEDURE' AND @object_name NOT LIKE 'P?_%' ESCAPE '?'THEN 'P_' + @object_name
WHEN @object_type = 'VIEW' AND @object_name NOT LIKE 'V?_%' ESCAPE '?' THEN 'V_' + @object_name
WHEN @object_type = 'USER_TABLE' AND @object_name NOT LIKE 'T?_%' ESCAPE '?' THEN 'T_' + @object_name
END
IF @new_object_name IS NOT NULL
BEGIN
EXEC sp_rename @object_name, @new_object_name
END
END |
Partager