Change Data Type

[Savil]

Hi All

does somebody have a script or example to search through all user tables in a database and change all smalldatetime fields to datetime?

Thanx

 

[gmmastros]

Run this in a query window:

Select  'Alter Table [' + T.Table_Name + '] Alter Column [' + C.Column_Name + '] DateTime'
From    Information_Schema.Tables T
        Inner Join Information_Schema.Columns C
          On T.Table_Name = C.Table_Name
Where   T.Table_Type = 'Base Table'
        And C.Data_Type = 'SmallDateTime'

You will see a series of alter table statements in the output window. You should be able to copy/paste the output to a new query window and run it.

I [!]STRONGLY[/!] encourage you to make a backup of your database before doing this.

You'll probably want to check through your code too. You could have procedures and/or functions that have parameters declared as SmallDateTime.

Select * 
From   Information_Schema.Routines 
Where  Routine_Definition Like '%smalldatetime%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Make sure your databases are NOT vendor/third-party databases. Changing structures of those could violate agreements.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875