Skip to content

Instantly share code, notes, and snippets.

@hlodwig
Created October 12, 2012 20:12
Show Gist options
  • Save hlodwig/3881254 to your computer and use it in GitHub Desktop.
Save hlodwig/3881254 to your computer and use it in GitHub Desktop.
TSQL - Drop constraints dynamically
--Check if exist foreing keys for determinated table
IF EXISTS(SELECT C.name
FROM { OKW.SYS.foreign_keys | OKW.SYS.default_constraints} C
INNER JOIN OKW.SYS.tables T ON C.parent_object_id = T.object_id
INNER JOIN OKW.SYS.schemas S ON T.schema_id = S.schema_id
WHERE T.name = 'MyTable')
BEGIN
DECLARE @CADENA NVARCHAR(MAX)
SET @CADENA = ''
DECLARE @LINEA NVARCHAR(255)
DECLARE cGen CURSOR FOR
SELECT ' ALTER TABLE ' + S.name + '.' + T.name + ' DROP CONSTRAINT ' + C.name AS 'LINEA_SELECT'
FROM { OKW.SYS.foreign_keys | OKW.SYS.default_constraints} C
INNER JOIN OKW.SYS.tables T ON C.parent_object_id = T.object_id
INNER JOIN OKW.SYS.schemas S ON T.schema_id = S.schema_id
WHERE T.name = 'MyTable'
OPEN cGEN
FETCH cGEN INTO @LINEA
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @CADENA = @CADENA + @LINEA
FETCH cGEN INTO @LINEA
END
CLOSE cGEN
DEALLOCATE cGEN
EXEC(@CADENA)
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment