![]() ![]() How to get Decimal/Float Output from Integers Divi.How to find String in All Stored Procedures in SQL.Get all Tables with Column Names and Data types fr.How to get Table Names with Record Count in SQL Se.How to Search Text in Steps of SQL Server Agent Jobs.What are the different ways to set value of a Var.How to create Database Snapshot - SQL Server DBA T.SQL Server 2014 Create Database Best Practices - S.How to Take Database Offline and Bring it Online. ![]() How to Shrink Database And Database Files - SQL Se.How to Create Database in SQL Server 2014 - SQL Se.How to Attach and Detach Databases - SQL Server DB.Check if column is removed and view is working correctly Msg 4502, LEVEL 16, State 1, Line 49 VIEW OR FUNCTION 'dbo.vw_Employee' has more COLUMN names specified than columns defined. Run the Select statement on view to get data after dropping column from base table In case, we add or drop column to base table that is used by view, we need to refresh the view definition. If we drop the column from Base Table/s, we will get error. Check if column is appearing in view correctly Check if we are getting StreetAddress column in dbo.vw_Employee Alter the base table that we used in the view Create view to get all columns and all records from dbo.Employee table INSERT INTO dbo.Employee VALUES ( 'Bob', 'Ladson' ) INSERT INTO dbo.Employee VALUES ( 'Aamir', 'Shahzad' ) Your view will not work without refreshing it. If you will drop the column from base table/s tables, you will get an error as you will see at the end of post. Below script can be used to learn step by step how this works and finally refresh the view by using sp_refreshview stored procedure. To include those columns in the view, we need to refresh the view definition after adding column/s to table/s. It will not include newly columns even you are using "*" in your query. When you use Select * in the view definition and columns will be added to base table/s later, the view will present the columns those were available at point of view creation. In this post we will learn how to refresh the definition of a view. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |