Quick ADD & ALTER Generator

,

Some times when we have two databases new_db and old_db , and we want to rapidly synchronize old_db ,

this script will help us to do the job.

--=====================
-- ADD 
--=====================
SELECT tmp.* from (
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ADD [' + COLUMN_NAME + '] ' + DATA_TYPE + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH IS NULL

UNION

SELECT 'ALTER TABLE [' + TABLE_NAME + '] ADD [' + COLUMN_NAME + '] ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(max)) + ')'  + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (NOT CHARACTER_MAXIMUM_LENGTH IS NULL) AND CHARACTER_MAXIMUM_LENGTH > 0

UNION
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ADD [' + COLUMN_NAME + '] ' + DATA_TYPE + '(max)'  + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (NOT CHARACTER_MAXIMUM_LENGTH IS NULL) AND CHARACTER_MAXIMUM_LENGTH = -1
) AS tmp
ORDER BY tmp.Q
--=====================
-- ALTER
--=====================
SELECT tmp.* from (
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH IS NULL

UNION

SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(max)) + ')'  + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (NOT CHARACTER_MAXIMUM_LENGTH IS NULL) AND CHARACTER_MAXIMUM_LENGTH > 0

UNION
SELECT 'ALTER TABLE [' + TABLE_NAME + '] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE + '(max)'  + CHAR(10)+'GO' + CHAR(10) AS Q
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (NOT CHARACTER_MAXIMUM_LENGTH IS NULL) AND CHARACTER_MAXIMUM_LENGTH = -1
) AS tmp
ORDER BY tmp.Q

Rate

3 (2)

Share

Share

Rate

3 (2)