Create a s.p., which can enable or disable all Referential Integrity
USE [ToolBox]
GO
/****** Object: StoredProcedure [dbo].[u_AllReferentialIntegrity] Script Date: 05/12/2015 11:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[u_AllReferentialIntegrity]
(
@Database NVARCHAR (100),
@Disable BIT
)
AS
BEGIN
----------------------------------------------------------------
-- DISABLE ALL REFERENTIAL INTEGRITY
DECLARE @NAME nvarchar(100), @STR nvarchar(300), @Script nvarchar(max)
SET @Script = 'DECLARE table_cursor CURSOR FOR ' +
'SELECT NAME FROM ' +@Database+ '.SYS.TABLES WHERE NAME NOT IN (''HL_Vietnam.BAK'', ''Lovells_Tokyo_DAKS.BAK'') ORDER BY NAME '
EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database
OPEN table_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM table_cursor
INTO @NAME
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
IF @Disable = 1
BEGIN
SET @STR = 'USE ' +@Database+ '; ALTER TABLE ' + @NAME + ' NOCHECK CONSTRAINT ALL'
END
ELSE
BEGIN
SET @STR = 'USE ' +@Database+ '; ALTER TABLE ' + @NAME + ' CHECK CONSTRAINT ALL'
END
PRINT @STR
EXECUTE sp_executesql @STR
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @NAME
END
CLOSE table_cursor
DEALLOCATE table_cursor
-- EXEC [u_AllReferentialIntegrity] @DATABASE = 'INPROASIA', @DISABLE = TRUE
END
沒有留言:
發佈留言