Disable and Enable Keys (with some validation)

,

Here are two stored procedures for enabling and disabling all keys (primary and foreign) for a specified database. It makes sure the database specified is not the system databases and the users session is at the DB to have its keys enables/disabled.

CREATE PROCEDURE [mig].[MIG_UTILITY_EnableKeys_sp] @p_dbname varchar(50) AS

DECLARE @v_error VARCHAR(100)

if @p_dbname =  (select  SD.name from master.dbo.sysprocesses SP, master.dbo.sysdatabases SD where SP.spid = @@SPID and SP.dbid = SD.dbid and  SD.name = @p_dbname and SD.name not in ('master', 'model', 'msdb'))
	exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT  all"
else begin
	set @v_error = 'The users current logged in database is not ' + @p_dbname + '.  Or you tried to run the script against a system database.'
	RAISERROR (@v_error, 16, 1)
end
GO


CREATE PROCEDURE [mig].[MIG_UTILITY_DisableKeys_sp] @p_dbname varchar(100) AS

DECLARE @v_error VARCHAR(100)

if @p_dbname =  (select  SD.name from master.dbo.sysprocesses SP, master.dbo.sysdatabases SD where SP.spid = @@SPID and SP.dbid = SD.dbid and SD.name = @p_dbname and SD.name not in ('master', 'model', 'msdb'))
	exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT  all"
else begin
	set @v_error = 'The users current logged in database is not ' + @p_dbname + '.  Or you tried to run the script against a system database.'
	RAISERROR (@v_error, 16, 1)
end
GO

Rate

Share

Share

Rate