Changing SQL data type 1

[Bluejay07]

Hello,

I want to verify and change an sql recordset column's data type at run time. For example, if rsTable.Fields(colName) = "Account" and dataType = varchar(10) then datatype = varchar(20).

I don't know what the syntax is for verifying the data type but I believe I would need something similar to the following to change it:

conn.Execute "ALTER TABLE TableName ALTER COLUMN ACCOUNT varchar(20)

Could someone please provide me with the proper syntax to verify and change the data type.

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

What type of database are you using?

SQL Server
Access
Oracle
MySQL
Etc....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Sorry, It will either be with SQL 2005 or SQL 2008.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

The following query should return the [!]code[/!] you need to alter the tables.

Select  'ALTER TABLE [' + C.TABLE_SCHEMA + '].[' + C.TABLE_NAME +']'
        + ' ALTER COLUMN ACCOUNT VarChar(20)' 
        + Case When C.IS_NULLABLE = 'YES' 
               Then ' NULL '
               Else ' NOT NULL '
               END AS AlterCode
From    INFORMATION_SCHEMA.COLUMNS C
        INNER JOIN INFORMATION_SCHEMA.TABLES T
          On C.TABLE_SCHEMA = T.TABLE_SCHEMA
          And C.TABLE_NAME = T.TABLE_NAME
          And T.TABLE_TYPE = 'BASE TABLE'
Where   COLUMN_NAME = 'Account'
        And DATA_TYPE = 'varchar'
        And CHARACTER_MAXIMUM_LENGTH < 20
        And COLUMN_DEFAULT Is NULL

This may return multiple rows, or no rows. Whatever is returned, you can loop through it and execute each one.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Thanks George. I'll try it out.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Thank you George.
Your code works perfectly and provides me with what I need.

If at first you don't succeed, then sky diving wasn't meant for you!