You are here:Home » statements » Script to generate ADD COLUMN statements for existing table - SQL Server

Script to generate ADD COLUMN statements for existing table - SQL Server

As we have seen some posts about replication, today I want to share a script which generates a tsql statement to add columns in an existing table. It helps when we need to add columns in an existing table from source and need to add in the destination. So let me share a query here,
SELECT 
'ALTER TABLE '+QUOTENAME(TABLE_NAME)
+' ADD '+QUOTENAME(COLUMN_NAME)+' ' 
+ QUOTENAME(DATA_TYPE)
+ CASE 
  WHEN DATA_TYPE LIKE '%CHAR%' THEN '('+REPLACE(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),'-1','MAX') +')' 
  ELSE '' 
 END 
+ CASE 
  WHEN DATA_TYPE IN ('NUMERIC','DECIMAL') THEN '('+CAST(NUMERIC_PRECISION AS VARCHAR(50)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(50)) + ')' 
  ELSE '' 
 END 
 + CASE 
  WHEN IS_NULLABLE = 'YES' THEN ' NULL' 
  ELSE ' NOT NULL' 
 END 
 + CASE 
  WHEN COLUMN_DEFAULT IS NULL THEN '' 
  ELSE ' DEFAULT '+ COLUMN_DEFAULT 
 END
 FROM INFORMATION_SCHEMA.COLUMNS  
 WHERE TABLE_NAME = '<TableName>' AND TABLE_SCHEMA = '<SchemaName>'
 GO
Note that this is a script about to generates add columns statements without primary key, foreign key ,check constraint and identity. Hope you like this and i request you to share any correction, enhanced script or any different script with full details for the same.