Expand the Intellisense options and select Refresh Local Cache as shown below:Īs soon as we refresh the cache, metadata is up-to-date (sometimes it takes a couple of seconds), and Intellisense removes the red squiggles line. An easy way to fix this, although it’s not an actual error (the above script can be executed without throwing an error), is to refresh the IntelliSense’s cache manually. Right after the creation of an object, when queried, it’s very likely for the view as the source to be underlined in red until the cache is refreshed. The same would have happened to the CREATE VIEW SQL statement. We just changed the name of the view and the metadata has not been updated yet. This indicates an error in SSMS, in this particular case about an invalid object name, but it’s not: I can’t help but notice the name of the view underlined in red. We could have just dropped the view and use the CREATE VIEW SQL statement to re-create it with the new definition, but we can’t break script and dependencies with this simple view, so it’s safe to just rename it using the stored procedure. The example below changes the database context to the SQLShackDB database and changes the view’s name: Make sure that you’re are connected to the SQLShackDB database or specify the database name in the USE statement to avoid getting errors from SQL Server. This is a series about using T-SQL, so we’re going to do it using the stored procedure mentioned above. How to rename database objects in a SQL Server safely – Part 2.How to rename database objects in a SQL Server database safely – Part 1.Both add-ins have a feature called Safe rename that renames tables, procedures, views, functions, and columns without breaking dependencies.įor detailed information about renaming SQL objects using this feature, see the following articles: I, personally, use SQL Server Management Studio and Visual Studio add-ins for SQL Server called ApexSQL Search and ApexSQL Refactor. If you want to play it safe, I’d suggest a third-party software solution for safely renaming SQL objects. Renaming views using a third-party software solution The smart thing to do would be to drop the object and re-create it with the new name using the CREATE VIEW SQL statement. Having said that, changing any part of an object name, including views, can break scripts and dependencies, which is why it’s not recommended to use this statement to rename views or any other user-created object. By definition, this SP is used for changing the name of a user-created object in the current database. Views are renamed using the sp_rename system stored procedure. This option can be useful when inserting data through a view which you’ll see later in this article. I also want to show you one neat thing that we can do WITH CHECK OPTION which is a part of the CREATE VIEW SQL syntax. However, before we actually insert data through a view, let’s see how we can rename a view. Now it’s time to start using Data Manipulation Language (DML) that is used to manipulate data itself and see how we can insert data into a table through a view. Furthermore, we got familiar with the ALTER VIEW statement used to change the output by changing the definition and structure of a query.Īlthough this article can be read independently from the first two, it’s highly advisable to head over and read the previous two parts to get the full picture and because it will be easier to follow along. In the second part about Modifying views in SQL Server, we upped the difficulty a little bit and created a more complex view with aggregates in it. To briefly summarize the series, in the first part about Creating views in SQL Server, the idea was to get familiar with the CREATE VIEW SQL syntax, all the different things we can do with views, and creating a really basic view. In this part, we’ll continue to work on views using the sample database and data that we created so far. So far, I’d say that we’re comfortable and familiar with the syntax, and we’ve learned how to create and modify views. This is the third article in a series of learning the CREATE VIEW SQL statement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |