CODEWITHSUNDEEP
sql-servertype-conversion
milo2011
milo2011

Reputation: 339

How to update the data type of same name multiple columns

I have a column called 'LastModifiedDate' of 'smalldatetime' data type.

This column is present in almost all of my tables in the database.

I want to change this column to 'datetime' data type.

Is there any way to do that with a sql script?

Upvotes: 1

Views: 65

Answers (1)

juergen d
juergen d

Reputation: 204784

To generate a script you could do

SELECT 
   'ALTER TABLE  [' + Table_Schema + '].[' + Table_Name + '] ' + 
   'ALTER COLUMN [' + Column_Name  + '] DATETIME;'       

FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'LastModifiedDate'

Upvotes: 2

Related Questions