Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

disable/enable constraints of all user tables

Status
Not open for further replies.

Moonrider

Programmer
Oct 30, 2001
12
0
0
BE

the following stored procedure
DISABLES constraints of all user tables :


Code:
CREATE PROCEDURE dbo.disable_constraints AS

DECLARE @tabelle varchar(50)
DECLARE tbl_tabellen CURSOR FOR 	
	select [name]
	from sysobjects
	where type = 'U' 

OPEN tbl_tabellen
FETCH NEXT FROM tbl_tabellen INTO @tabelle
WHILE @@fetch_status = 0
begin
    EXEC('ALTER TABLE ' + @tabelle + ' NOCHECK CONSTRAINT ALL')
    PRINT 'disabled constraints of ' + upper(@tabelle)
    FETCH NEXT FROM tbl_tabellen INTO @tabelle
end
CLOSE tbl_tabellen
DEALLOCATE tbl_tabellen
GO


the following stored procedure
ENABLES constraints of all user tables :


Code:
CREATE PROCEDURE dbo.enable_constraints AS

DECLARE @tabelle varchar(50)
DECLARE tbl_tabellen CURSOR FOR 	
	select [name]
	from sysobjects
	where type = 'U' 

OPEN tbl_tabellen
FETCH NEXT FROM tbl_tabellen INTO @tabelle
WHILE @@fetch_status = 0
begin
    EXEC('ALTER TABLE ' + @tabelle + ' CHECK CONSTRAINT ALL')
    PRINT 'enabled constraints of ' + upper(@tabelle)
    FETCH NEXT FROM tbl_tabellen INTO @tabelle
end
CLOSE tbl_tabellen
DEALLOCATE tbl_tabellen
GO


have a blessed day :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top