How exactly can I refresh the view after adding a default constraint to one of the tables that makes up the view? I tried to use the sp_refreshview stored procedure but that didn't actually update the view. Here is the code I wrote to update one of columns with a default value:
ALTER TABLE chosenTable ADD CONSTRAINT defaultConstraint DEFAULT '1' FOR selectedColumn
Here is the code I used to see whether or not the constraint is there before and after running the above code:
SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name = 'selectedColumn' AND object_id = object_id('choseTable')
The previous statement can be used to see if the column of that name has been updated in the view as well. The problem is that the column has the constraint in the table itself, but the constraint is not added to the view. After trying the sp_refreshview and having no success, I'm stuck. I would appreciate any tips that don't outright involve dropping the view and recreating it. But if that is the only solution, is it simply a matter of using the DROP command and then making the view like normal?
After dropping the view, here is my attempt to recreate it. It doesn't allow me to recreate it:
CREATE VIEW newView AS SELECT * FROM table1 UNION ALL SELECT * FROM table2 UNION ALL SELECT * FROM table3 UNION ALL SELECT * FROM table4
It says that the synax is incorrect and that 'CREATE VIEW' must be the only statement in the batch. Trying to run it says that all queries combined using a UNION must have an equal number of expressions in their target lists. However, I'm following the query format in the official documentation. What can I try to fix this?