0

I need to change datatype of all columns in database where datatype is char(255) to nvarchar(255). To do that, I need to remove P/F keys and then recreate them again. But on datatype change, I loose not null information.

Some columns may be nullable, and some not. This is the problem.

How can I solve it?

EDIT:

When I drop P/F keys, I try to change datatype successfully. And then, when recreating keys it says that cannot set key on that field because it is nullable.

Then I tried to do it with try/catch:

try to add key, and if not, change datatype with not null, and try again adding key.

It works, but now throws tons of some exception. I think the job is done, but I am not sure because my DB has about 100 tables and cannt check all of them if they are ok.

So is here some other way to do this?

Aleksandar
  • 1,163
  • 22
  • 41
  • Google and elbow grease? Maybe you should start with what you've tried. – Eric Stein May 01 '14 at 14:59
  • You loose not null information where exactly? Your primary key column cannot possibly be null right now. Your Foreign key might be nullable but then if it is, you have no problem in loosing the not null information. So.. where exactly that is a problem? – Praba May 01 '14 at 15:00
  • Edited. I googled but unsuccessfully. – Aleksandar May 01 '14 at 15:10

1 Answers1

1

You can use the INFORMATION_SCHEMA.COLUMNS view to find all columns that are char(255). The view also contains additional information about the column, such as what table it belongs to and whether or not it allows null.

For example...

select TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE = 'char'
And CHARACTER_MAXIMUM_LENGTH = 255

You can use that information to determine what columns need to be modified and you know whether or not it allowed null.

Michael
  • 599
  • 6
  • 11