How to alter multiple columns with T-SQL(sort of)

--//1. Create a table. I see this a lot :-(.
CREATE TABLE myLameTbl(
ID INT Identity(1,1)
, FirstName VarChar(10)
, LastName VarChar(15)
, FavoriteURL1 VarChar(45)
, FavoriteURL2 VarChar(45)
, FavoriteURL3 VarChar(45)
, FavoriteURL4 VarChar(45)
, FavoriteURL5 VarChar(45)
)

--//2. insert some data
INSERT INTO myLameTbl
SELECT
'Bob'
,'Mihada'
,'http://www.databasejournal.com'
,'http://www.google.com/'
,'http://www.turnkeylinux.org/'
,'http://www.facebook.com/'
,'http://www.bobmihada.com/'

SELECT * FROM myLameTbl

--// 3. Unknowningly insert more than table allows.
--// Coming soon: String or binary data would be truncated.
UPDATE myLameTbl
SET FavoriteURL5 = 'http://www.bobmihada.com/2010/11/how-to-alter-multiple-columns-with-t.html'

--// 4. Curse the developer who created said table.
--// 5. Get on with it. Mitigate risk with this script. Review/Execute the printed command.
--// Alters that increase character size should take seconds.
--// As oppose to the SSMS GUI, design and save, always timesout.
--//---------------------------------------------------
DECLARE @SXML as VARCHAR(MAX)
SELECT @SXML = REPLACE((
SELECT
'ALTER TABLE ' + TABLE_NAME
+ ' ALTER COLUMN ' + COLUMN_NAME
+ ' VARCHAR(255)
GO
'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='myLameTbl'
AND COLUMN_NAME LIKE 'FavoriteURL[1-9]'
FOR XML PATH('')
),'
','')
PRINT @SXML
--//---------------------------------------------------
--//---------------------------------------------------

--// 6. review your change and smile.
SELECT
TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME ='myLameTbl'
AND COLUMN_NAME LIKE 'FavoriteURL[1-9]'

--//7. lets try step #3 again
UPDATE myLameTbl
SET FavoriteURL5 = 'http://www.bobmihada.com/2010/11/how-to-alter-multiple-columns-with-t.html'

Comments :

0 comments to “How to alter multiple columns with T-SQL(sort of)”