0

I'm in a process to change database collations. I could create ( and copy ) scripts to create/drop FKS, PKS, but i'm having problems with unique keys and check keys.

I got this query:

 SELECT

     'ALTER TABLE  ' +
     QuoteName(OBJECT_NAME(so.parent_obj)) +
     CHAR(10) +
     ' DROP CONSTRAINT ' +
     QuoteName(CONSTRAINT_NAME)
 FROM
     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
     INNER JOIN sys.sysobjects so
     ON cc.CONSTRAINT_NAME = so.[name]

But, I can't figure it out how to implement SCHEMA on it.

I'm trying something like thi ( this is one of dozens i've tried ):

SELECT
      'ALTER TABLE  ' + quotename(sh.name)+ QuoteName(OBJECT_NAME(so.parent_obj)) +
      CHAR(10) +
      ' DROP CONSTRAINT ' +  QuoteName(CONSTRAINT_NAME)
  FROM
      INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
      INNER JOIN sys.sysobjects so
        ON cc.CONSTRAINT_NAME = so.[name]
    inner join sys.schemas sh
        on sh.schema_id=so.schema_id

but obviously, it has columns name error.

EDIT1:

I think what I did here is right:

select
    'alter table '+SCHEMA_NAME(schema_id)+'.'+OBJECT_NAME(parent_object_id) +
    char(10)+
    ' drop constraint '+OBJECT_NAME(OBJECT_ID)
    from sys.objects where type_desc='CHECK_CONSTRAINT'
  • Are you trying to do something like this? mssqltips.com/sqlservertip/3347/… – Erik Darling May 18 '17 at 16:10
  • Hey again Erik. Yes, But I have already made the FK script to drop/recreate. ( and I think I got it from the same website ). the problem is with the Check Constraints. PK, FK, Indexes I could manage to use scripts to drop/recreate. I just edit the question with a query I made, – Racer SQL May 18 '17 at 16:16
  • 2
    Why not use sys.check_constraints? – Erik Darling May 18 '17 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.