/* 
	ControlProcedureSys.sql
	Stored procedure generation statements for the Control System database schema. These are system catalog specific.
	Previously the were added to ControlProcedures.sql but 2 were added in 8.2 that are needed by RunUpgrades.sql.
*/

PRINT 'Adding system catalog stored procedures' 
go

use control
go

IF OBJECT_ID('NV_DropColumnConstraints') IS NOT NULL
  DROP PROCEDURE NV_DropColumnConstraints
GO

/* This does not drop default constraints, use NV_DropColumnDefaultConstraints*/
CREATE PROCEDURE NV_DropColumnConstraints
	@tableName varchar(200), @columnName varchar(200), @returnStatus int OUTPUT
AS
BEGIN
	SET @returnStatus = 0;
	DECLARE @selectCmd nvarchar(200);
	DECLARE @cursorCmd nvarchar(400);
	DECLARE @constraintName varchar(40);

	SET @selectCmd = 'SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ' +
		' WHERE TABLE_NAME = ''' +  @tableName +
		''' AND COLUMN_NAME = ''' + @columnName + '''';
	SET @cursorCmd =' DECLARE constraintCursor cursor for ' + @selectCmd;
	EXEC sp_executesql @cursorCmd;

	OPEN constraintCursor;

	fetch next from constraintCursor into @constraintName;	
	PRINT 'FETCH_STATUS: ' +CAST(@@FETCH_STATUS AS VARCHAR);

	DECLARE @SQLCmd varchar(200);
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		IF (@constraintName IS NOT NULL) OR (LEN(@constraintName ) > 0)
		BEGIN					
			SET @SQLCmd = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName ;
			EXEC (@SQLCmd);
			SET @returnStatus = 1;
		END
		FETCH NEXT FROM constraintCursor INTO @constraintName;
	END

	CLOSE constraintCursor ;
	DEALLOCATE constraintCursor ;	

    IF @returnStatus = 0
		PRINT 'NV_DropColumnConstraints failed.' 

END
GO

IF OBJECT_ID('NV_DropColumnDefaultConstraint') IS NOT NULL
  DROP PROCEDURE NV_DropColumnDefaultConstraint
GO

CREATE PROCEDURE NV_DropColumnDefaultConstraint
	@tableName varchar(200), @columnName varchar(200), @returnStatus int OUTPUT
AS
BEGIN
	SET @returnStatus = 0;
	DECLARE @selectCmd nvarchar(1000);
	DECLARE @cursorCmd nvarchar(1000);
	DECLARE @constraintName varchar(40);

	SET @selectCmd = 'SELECT o.name FROM sysobjects o ' + 
				' INNER JOIN syscolumns c ON o.id = c.cdefault ' +
				' INNER JOIN sysobjects t ON c.id = t.id ' +
				' WHERE o.xtype = ''D'' AND t.name = ''' + @tableName + ''' AND c.name = ''' + @columnName + '''';

	SET @cursorCmd =' DECLARE constraintCursor cursor for ' + @selectCmd;
	EXEC sp_executesql @cursorCmd;

	OPEN constraintCursor;

	fetch next from constraintCursor into @constraintName;	
	PRINT 'FETCH_STATUS: ' +CAST(@@FETCH_STATUS AS VARCHAR);

	DECLARE @SQLCmd varchar(200);
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		IF (@constraintName IS NOT NULL) OR (LEN(@constraintName ) > 0)
		BEGIN					
			SET @SQLCmd = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName ;
			EXEC (@SQLCmd);
			SET @returnStatus = 1;
		END
		FETCH NEXT FROM constraintCursor INTO @constraintName;
	END

	CLOSE constraintCursor ;
	DEALLOCATE constraintCursor ;	

    IF @returnStatus = 0
		PRINT 'NV_DropColumnDefaultConstraint failed.' 

END
GO
