How to make this query, to INNER JOIN with the schema?

Posted: edited May 18 at 16:14 - Source : stackoverflow

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'