Sometimes it is necessary to drop all the primary key, foreign key or unique constraint from a specified table. Here is this article I am preparing a T-SQL stored procedure to clean the constraint according to your needs.
It takes Database name, table name, constraint name and Type of constraint. If we not provide the Table name it means all the table are affected in specified DB and if we not provide the constraint name it means the entire constraint name are affected Or we can specified the Type of the constraint without providing constraint name.
To know about all the constraint in a specified Table uses these SQL statements.
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='my_table'
As the foreign key dependency is there u must technically map the table objects.
It is a proto type only and u must develop it according to your needs.
IF EXISTS (SELECT *
FROM sysobjects
WHERE type = 'P'
AND name = 'up_RUN_CONSTRAINTCLEANER')
BEGIN
DROP PROCEDURE up_RUN_CONSTRAINTCLEANER
END
GO
CREATE Procedure [dbo].[up_RUN_CONSTRAINTCLEANER]
(
@P_DBName VARCHAR(MAX) = NULL,
@P_TBlName VARCHAR(MAX) = NULL,
@P_ConsName VARCHAR(MAX) = NULL,
@P_ConsType VARCHAR(MAX) = NULL -- P, F, U
)
AS
DECLARE @SQL NVARCHAR(MAX)
DECLARE @CTYPE NVARCHAR(MAX)
BEGIN
IF ISNULL(@P_DBName,'')=''
BEGIN
PRINT 'Database name can not be blank'
END
ELSE
BEGIN
IF ISNULL(@P_TBlName,'')=''
WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
EXEC SP_EXECUTESQL @SQL
END
END
IF ISNULL(@P_TBlName,'')<>''
BEGIN
WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
AND B.NAME=@P_TBlName)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
EXEC SP_EXECUTESQL @SQL
END
END
ELSE
BEGIN
IF ISNULL(@P_ConsName,'')<>''
BEGIN
WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME, a.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
AND b.NAME=@P_TBlName
AND a.CONSTRAINT_NAME=@P_ConsName)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
AND a.CONSTRAINT_NAME=@P_ConsName
EXEC SP_EXECUTESQL @SQL
END
END
ELSE
BEGIN
IF ISNULL(@P_ConsType,'')<>''
BEGIN
IF @P_ConsType='P'
BEGIN
SET @CTYPE='PRIMARY KEY'
END
IF @P_ConsType='F'
BEGIN
SET @CTYPE='FOREIGN KEY'
END
IF @P_ConsType='U'
BEGIN
SET @CTYPE='UNIQUE'
END
WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
AND b.NAME=@P_TBlName
AND a.CONSTRAINT_NAME=@P_ConsName)
BEGIN
SELECT @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN sys.tables b ON a.TABLE_NAME=b.name
WHERE a.CONSTRAINT_CATALOG = @P_DBName
AND a.CONSTRAINT_TYPE =@CTYPE
EXEC SP_EXECUTESQL @SQL
END
END
END
END
-- PRINT 'SUCCES -- Constraint Removed'
END
/*
-- Settings-1
EXEC up_RUN_CONSTRAINTCLEANER]
@P_DBName = 'my_db',
@P_TBlName = NULL,
@P_ConsName = NULL,
@P_ConsType = NULL -- P, F, U
-- Settings-2
EXEC up_RUN_CONSTRAINTCLEANER
@P_DBName = 'my_db',
@P_TBlName = 'my_table',
@P_ConsName = NULL,
@P_ConsType = NULL -- P, F, U
-- Settings-3
EXEC up_RUN_CONSTRAINTCLEANER]
@P_DBName = 'my_db',
@P_TBlName = 'my_tab',
@P_ConsName = 'FK_my_table',
@P_ConsType = NULL -- P, F, U
-- Settings-4
EXEC up_RUN_CONSTRAINTCLEANER]
@P_DBName = 'my_db',
@P_TBlName = 'my_tab',
@P_ConsName = '',
@P_ConsType = 'F' -- P, F, U
*/
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment