Primary key violations when changing Sort order

[JBell]

We are setting up a new server with DOCI sort order and will be moving the data from a Binary server. The source server is SQL 7 then the destination is SQL 2000. Moving the data is not a major problem using BCP but there is the potential to have duplicate records when the case is ignored (i.e. the orginal server would have seperate records for abc & Abc but when we transfer these records to a case insinsitive enviroment they will cause a violation of hte primary key) I would like to determine a way to write a cursor or some other means to report all of these cases.

 

[fluteplr]

select upper(mykey), count(*) from mytable
group by upper(mykey)
having count(*) > = 1
order by upper(mykey)

run that for all your tables and you should have a listing of all your keys that are duplicated.

 

[JBell]

thanks a lot this is a huge step. but I have only one other question I have to review over 32 databases with 900 tables each and each table has several indexes. I can write a cursor to read the tables and the index id's but to use this great piece of code you sent I need to retreive the field names that are included in the index. From looking at sysindexes I do not know what fields are referenced. Do you have any help for me to return the field names ?

 

[fluteplr]

I have to run so this is going to be short.

Have a look at the information_schema views.

These look like they would be useful.

INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

You should be able to use data from these to create the select statements and then run those.

If this does not get you going post again I will be on again sometime tonight (late).