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>' GONote 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.
Comments
Post a Comment