Re: Consider table specific variable names.

[Jun Li]

Yes, sure. Can you perhaps send me the SQL so that I can debug this ?

Tom

Here is a portion of the script.

 

Thanks.

--
--------------
Cheers,
Jun

-- ----------------------------------------------------------------------- 
-- WizardType 
-- ----------------------------------------------------------------------- 

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'WizardType')
BEGIN
  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 'WizardType'  
OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tablename, @constraintname
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '[EMAIL PROTECTED]' DROP CONSTRAINT '[EMAIL PROTECTED])
      FETCH NEXT FROM refcursor INTO @tablename, @constraintname
    END
  CLOSE refcursor
  DEALLOCATE refcursor
  DROP TABLE WizardType
END;

-- ----------------------------------------------------------------------- 
-- WizardQuestionType 
-- ----------------------------------------------------------------------- 

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 
'WizardQuestionType')
BEGIN
  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 
'WizardQuestionType'  OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tablename, @constraintname
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '[EMAIL PROTECTED]' DROP CONSTRAINT '[EMAIL PROTECTED])
      FETCH NEXT FROM refcursor INTO @tablename, @constraintname
    END
  CLOSE refcursor
  DEALLOCATE refcursor
  DROP TABLE WizardQuestionType
END;

-- ----------------------------------------------------------------------- 
-- WizardQuestionFavourite 
-- ----------------------------------------------------------------------- 

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 
'WizardQuestionFavourite')
BEGIN
  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 
'WizardQuestionFavourite'  OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tablename, @constraintname
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '[EMAIL PROTECTED]' DROP CONSTRAINT '[EMAIL PROTECTED])
      FETCH NEXT FROM refcursor INTO @tablename, @constraintname
    END
  CLOSE refcursor
  DEALLOCATE refcursor
  DROP TABLE WizardQuestionFavourite
END;

-- ----------------------------------------------------------------------- 
-- WizardQuestion 
-- ----------------------------------------------------------------------- 

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 
'WizardQuestion')
BEGIN
  DECLARE @tablename nvarchar(256), @constraintname nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid
    WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = 
'WizardQuestion'  OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tablename, @constraintname
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '[EMAIL PROTECTED]' DROP CONSTRAINT '[EMAIL PROTECTED])
      FETCH NEXT FROM refcursor INTO @tablename, @constraintname
    END
  CLOSE refcursor
  DEALLOCATE refcursor
  DROP TABLE WizardQuestion
END;