CODEWITHSUNDEEP
sql-server
vero
vero

Reputation: 1015

Convert types of column SQL Server

I have more than 80 tables in my SQL Server database. I have more than 500 column. I want change some types of columns to free the space.

I wrote this script just to see the column that beginner by nvar and nch.

WHILE (SELECT * 
       FROM DATA.INFORMATION_SCHEMA.COLUMNS
       WHERE (DATA_TYPE LIKE 'nvar%' OR DATA_TYPE LIKE 'nch%'))
       --OR DATA_TYPE LIKE 'float%')
BEGIN
    PRINT 'change type in SCHEMA';
END

I should add to this script or I write another to convert the types automatically.

Someone please can help me, how can I do it ?

Thanks a lot.

Upvotes: 0

Views: 140

Answers (1)

KtX2SkD
KtX2SkD

Reputation: 752

Provided you considered the concerns raised by the commenters, this might be what you're looking for. The script will prepare SQL statements for your use, based on your referenced table's data.

Please double-check its correctness though. Also, I assumed you want new lines and GO's, but you can easily remove those if you didn't want them.

SELECT
    AlterSql = CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME, ' ', SUBSTRING(DATA_TYPE, 2, LEN(DATA_TYPE)), '(', CHARACTER_MAXIMUM_LENGTH, ') ', CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'NO' THEN 'NOT NULL' ELSE 'ERROR' END, CHAR(13), CHAR(10), 'GO')
  , *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('nchar', 'nvarchar')

Upvotes: 1

Related Questions