[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

change a coluimn in a SQL table while it is referenced by view

Hi

I need to change one of the columns in a major table , from INT to REAL. When I do that in SMSS I get the notification that I can not change the table because it is referenced to one or more views (is actually referenced to about 10 views).

Is there any way to change that column's data type to something else without have to delete and alter recreate all those views ?

happy to do in T-sql instead of SMSS ...
0
LukeB
Asked:
LukeB
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume the view is created WITH SCHEMABINDING.
in which case: you CANNOT change the table without recreating the view.
0
 
reazonCommented:
I don't think there's an easy way to do that.  Here's how I'd do it:

1. From SSMS, generate CREATE scripts for those views and save the scripts
2. Change the column type for that column in all of those scripts
3. Drop the views
4. Modify the table to change the column type
5. Run the saved CREATE VIEW scripts to recreate the views

HTH
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now