2015年5月12日星期二

TSQL - s.p. to enable and disable all Referential Integrity

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 


沒有留言:

發佈留言