Altering the Data Type of Table and View

Question

I am having a big database with lots of view and tables.

now in many tables and views the datatype of column , named 'Company' is INT

with time the data changed and now we want 'Company' column to hold character value.

Can I write a cursor to change the datatype of all this tables and Views, Because manually changing the datatype is time consuming.

I tried modifying datatype with the help of this link: How to Change All Sql Columns of One DataType into Another

But this is working only for table , I am not able to change the datatype of Views

Thanks in advance for your help!

Answer 1

If you do not see the correct data type in your views, then you need to recompile them. SQL Server is not very smart about invalidating views if dependent objects change. E.g. if you have a view that select * (which you anyways should not do) from a table and you add a column, the view will not reflect the new column until you recompile it. The same is true for other changes to dependent objects.

To recompile the view use the stored procedure sp_refreshview. This MSDN page describes the procedure and also has a script at the bottom of the page that allows you to bulk refresh all your views.

Answer 2

First of all, you don't need to modify anything in view. Any change in corresponding table will automatically reflect in your view.

If not, it might be because if a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried. SO as suggested by @Ralf, I +1 that answer, use sp_refreshview to update view.

For your comment ALTER TABLE ALTER COLUMN Company failed because one or more objects access this column. This is because some constraint or index is using that column you have to first drop that and then only you might be able to alter that column.

You can find related constraint using query:

select  db_name()    as CONSTRAINT_CATALOG
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)    as CONSTRAINT_SCHEMA
,c_obj.name  as CONSTRAINT_NAME
,col.name    as COLUMN_NAME
,col.colid   as ORDINAL_POSITION
,com.text    as DEFAULT_CLAUSE

from    sysobjects  c_obj
join syscomments    com on c_obj.id = com.id
join sysobjects t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid
where
c_obj.uid   = user_id()

Drop any index and/or constraint on column & then try alter, it must work then.

Also refer answers to this question Drop a column from table problem (SQL Server 2008)

Hope it helps.