Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.

I want to alter all columns of that type in any table in this database.

How can I do this?

share|improve this question
You want to say that you want to alter the data type of a column from DATE to CHAR(10) in a script and not in Management Studio? For a single column, all columns in a single table, all columns of all tables in your database? It's not very clear, so please try to get more details. Have you tried anything? – Marian Feb 13 '13 at 8:03
@Marian ,I explain more and edit my question.I can do it for one table but I don't know how do it for all (or many) tables. – Hamid Talebi Feb 13 '13 at 8:21
up vote 0 down vote accepted

A piece of code that does what you want is:

declare @sql nvarchar(max), @crlf char(2) = CHAR(13)+CHAR(10);

select @sql = isnull(@sql, '') + 'alter table ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' alter column ' + c.COLUMN_NAME + ' CHAR(10)' + 
    + case when c.IS_NULLABLE = 'YES' then ' NULL;' else ';' end + @crlf
where DATA_TYPE = 'date'

print @sql;
-- exec sp_executesql @sql --execute only after inspection!

But I would like to stress that you shouldn't normally do it on a system (except for dev/test purposes), because if it is Date information that you're using, you should be using the proper data type and avoid any other unnecessary cast.

share|improve this answer
,Don't worry!I test this snippet code on a test DB at first time, then use it.BTW thanks so much.;) – Hamid Talebi Feb 13 '13 at 13:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.