May 10, 2011 at 2:12 pm
Hi friends,
We need to change the collation on some of the fields in the database. Do I need to drop the constraints before changing the collation or will it work if I just disable constraint before changing collation?
alter table lmd alter column col1 varchar(30) collate SQL_Latin1_General_CP1_CI_AS NULL;
Msg 5074, Level 16, State 1, Line 2
The object 'lmdcol1_ck' is dependent on column 'col1'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.
I disabled the constraint:
ALTER TABLE lmd NOCHECK CONSTRAINT lmdcol1_ck;
Command(s) completed successfully.
Ran the alter command again to change the collation, still getting the same error..
If I change it manually by right clicking on table and design table - type in the collation name for the column & click save. I get this warning:
'LMD' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be re-created with ANSI_NULLS 'on'.
IF I click yes, it changes the collation. But I'm not sure if this will affect any data in the table..
Any help please? Thanks
May 11, 2011 at 6:54 am
Any suggestions please?
Thanks a lot
May 11, 2011 at 6:58 am
Judging by the name, 'lmdcol1_ck' is a check constraint. You have to remove constraints and indexes referencing the column before you change its collation. If you have to do it en masse, please read very good post of Louis Davidson: http://sqlblog.com/blogs/louis_davidson/archive/2011/04/08/the-process-of-changing-the-collation-for-a-database-and-objects.aspx
Regards
Piotr
...and your only reply is slàinte mhath
May 11, 2011 at 9:14 am
Thanks for your reply. Yes it is check constraint.
We have a few char/varchar/nchar columns that we would like the collation to be changed.. To remove the constraints & indexes referencing these columns, I'm trying to write a script so I don't have to run drop statements for each one of them.. I can get all the fields that want to change the collation for from this sql:
select sysobjects.name tablename,syscolumns.name columname,syscolumns.collation,systypes.name datatype
from syscolumns
inner join systypes on syscolumns.xtype = systypes.xtype
inner join sysobjects on syscolumns.id = sysobjects.id
inner join sysusers on sysobjects.uid = sysusers.uid
where systypes.name in ('varchar', 'char', 'nchar', 'nvarchar')
and sysobjects.xtype in ('U')
and syscolumns.collation not like 'SQL_Latin1_General_CP1_CI_AS'
The result from the above query gives the columns that collation has to be changed for. I need to script the drop statements for indexes/constraints referenced by these columns.. I'm able to drop all the check constraints in the database:
select 'ALTER TABLE ' + sys.schemas.name+'.'+OBJECT_NAME(sys.objects.parent_object_id) + ' DROP CONSTRAINT ' +OBJECT_NAME(OBJECT_ID)
FROM sys.objects
join sys.schemas on sys.schemas.schema_Id = sys.objects.schema_id
WHERE sys.objects.type_desc LIKE '%CHECK_CONSTRAINT'
But I would want to drop only those that are referenced by the columns from query 1.. I tried joining syscolumns table to the query2 but it is working.. I would appreciate any help please.
Thanks again.
May 11, 2011 at 9:32 am
If you are using SSMS 2008 you can try to script the table containint the columns as DROP..CREATE script. SSMS generates separate drop and create statements for all constraints on the table.
You can use this approach if you find it useful.
Alternatively look at the procedures in the post I mentioned earlier. They generate DROP statements and execute them. You can probably modify them to generate CREATE script as well.
Regards
Piotr
...and your only reply is slàinte mhath
May 11, 2011 at 10:49 am
Thanks... SSMS is very helpful but since we will have to run this on a few environments we need to script it.. I tried the below query, it works to drop the check constraint but I'm getting duplicate query results...
select 'ALTER TABLE ' + s.name+'.'+OBJECT_NAME(o.parent_object_id) + ' DROP CONSTRAINT ' +OBJECT_NAME(OBJECT_ID)
FROM sys.objects o, sys.sysobjects so, sys.syscolumns c,sys.systypes t, sys.sysusers u,sys.schemas s
WHERE o.type_desc LIKE '%CHECK_CONSTRAINT'
and s.schema_Id = o.schema_id
and c.xtype = t.xtype
and c.id = so.id
and so.uid = u.uid
and t.name in ('varchar', 'char','nchar','nvarchar')
and so.xtype in ('U')
and c.collation != 'SQL_Latin1_General_CP1_CI_AS'
and OBJECT_NAME(o.parent_object_id) like 'CM%'
order by OBJECT_NAME(o.parent_object_id)
Is it possible to get only one row and not duplicate the results for the above query?
Thanks a lot
May 11, 2011 at 11:23 am
How about this
select 'alter table ' +
quotename(object_schema_name(parent_object_id)) +
'.' + quotename(object_name(parent_object_id)) +
' drop constraint ' + quotename(name)
from sys.objects where name in
(select constraint_name from information_schema.check_constraints)
Regards
Piotr
...and your only reply is slàinte mhath
May 11, 2011 at 11:50 am
It works but this generates script for all the columns.. We need the results only for specific tables with the collation !SQL_Latin1_General_CP1_CI_AS for the character columns... I tried the below:
SELECT
'ALTER TABLE ' +
QuoteName(OBJECT_NAME(o.parent_obj)) +
CHAR(10) +
' DROP CONSTRAINT ' +
QuoteName(CONSTRAINT_NAME)
FROM
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
JOIN sys.sysobjects o ON cc.CONSTRAINT_NAME = o.[name]
where cc.CONSTRAINT_NAME IN (
select ccc.name
from sys.check_constraints ccc
inner join sysobjects o on ccc.parent_object_id = o.parent_obj
inner join syscolumns c on c.id = o.id
inner join systypes s on c.xtype = s.xtype
where s.name in ('varchar', 'char','nchar','nvarchar')
and o.xtype in ('U')
and c.collation <> 'SQL_Latin1_General_CP1_CI_AS'
)order by OBJECT_NAME(o.parent_obj)
The above query generates script for all the tables, but we need only for the char columns that are not 'SQL_Latin1_General_CP1_CI_AS'.. Any ideas?
Appreciate your help very much.
May 11, 2011 at 12:33 pm
Please have a look at
select * from information_schema.columns
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply