0

I have a list of tables where i have to change VARCHAR datatype of all columns no NVARCHAR datatype.

While changing to NVARCHAR i should get all those not null and null columns. It should change the size of columns having >4000 to NVARCHAR(MAX) and want to change the size of primary key columns to 448 when converted to NVARCHAR.The primary key columns cause problem while converted to NVARCHAR when the size is above 448 due to index errors

Axs
  • 755
  • 2
  • 14
  • 26

1 Answers1

1

This query will give you a list of all varchar columns with length and nullability

SELECT TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       CHARACTER_OCTET_LENGTH,
       IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE='varchar'

Your question is unclear, for me to help more.

While changing to NVARCHAR i should get all those not null and null columns

Does this mean you want to retain nullability property as is? Please ask with more clarity.

Raj
  • 10,653
  • 2
  • 45
  • 52
  • what you are saying is correct.But i want to change the width of the primary key column having nvarchar type to 248 . is it possible to get it through query. While changing to NVARCHAR i should get all those not null and null columns – Axs Feb 20 '14 at 09:07
  • You cannot change the datatype of the `PK Column`, without dropping the `PK Constraint` – Raj Feb 20 '14 at 09:21
  • Hi Raj, i am changing the datatype to nvarchar after dropping all constraints in the database. – Axs Feb 20 '14 at 09:22
  • Hi Raj,is it required to consider the dependencies while executing alter table scripts to change varchar to nvarchar datatype of the columns. i am getting some error like ALTER TABLE ALTER COLUMN "Product_Number" failed because one or more objects access this column. i have dropped all the constraints but am getting the error – Axs Feb 20 '14 at 09:36
  • Drop `FK constraints`, `PK constraints` and `Default Constraint` – Raj Feb 20 '14 at 10:18