60 lines
No EOL
2 KiB
Transact-SQL
60 lines
No EOL
2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS dev.uspIntezmenyDisableObjects
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspIntezmenyDisableObjects
|
|
@pIsIndex bit = 1
|
|
,@pIsConstraint bit = 1
|
|
,@pIsTrigger bit = 1
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE
|
|
@sql nvarchar(max)
|
|
,@message nvarchar(max)
|
|
-- ================================================================== --
|
|
-- INDEX, TRIGGER, CONSTRAINT kikapcsolás --
|
|
-- =================================================================== --
|
|
|
|
IF @pIsIndex = 1 BEGIN
|
|
|
|
SET @sql = N''
|
|
|
|
SELECT @sql += N'
|
|
ALTER INDEX ' + i.name + ' ON ' + t.name + N' DISABLE;'
|
|
FROM sys.indexes i
|
|
INNER JOIN sys.tables t ON t.object_id = i.object_id
|
|
WHERE i.type = 2 /*non clustered*/
|
|
AND t.name LIKE 'T[_]%' AND t.name <> 'T_ENTITYHISTORY'
|
|
AND t.schema_id = 1
|
|
|
|
SET @message = 'INDEX DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'INDEX DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
END
|
|
|
|
IF @pIsConstraint = 1 BEGIN
|
|
SET @sql = N''
|
|
|
|
SELECT @sql = @sql + N'
|
|
ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(fk.object_id) + CHAR(13) + CHAR(10)
|
|
FROM sys.foreign_keys fk
|
|
WHERE OBJECT_NAME(fk.parent_object_id) LIKE 'T[_]%'
|
|
|
|
SET @message = 'CONSTRAINT OFF... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'CONSTRAINT OFF DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
END
|
|
|
|
IF @pIsTrigger = 1 BEGIN
|
|
SET @message = 'TRIGGER DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_msforeachtable 'DISABLE TRIGGER ALL ON ?'
|
|
SET @message = 'TRIGGER DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
END
|
|
END
|
|
GO |