Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Deleting a Table That Is Used by a View

By: FYIcenter.com

(Continued from previous topic...)

What Happens If You Delete a Table That Is Used by a View?

Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble.

But that view will become invalid. The tutorial exercise below shows you what happens to the view, when the underlying table is deleted:

USE FyiCenterData;
GO

SELECT * INTO fyi_links_copy 
   FROM fyi_links WHERE counts > 0;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
50015

DROP TABLE fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fyi_links_copy'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'fyi_links_view' 
   because of binding errors.

(Continued on next topic...)

  1. What Are Views?
  2. How To Create a View on an Existing Table?
  3. How To See Existing Views?
  4. How To Drop Existing Views from a Database?
  5. How To Get a List of Columns in a View using "sys.columns"?
  6. How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
  7. How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
  8. How To Generate CREATE VIEW Script on an Existing View?
  9. How To Get the Definition of a View Out of the SQL Server?
  10. Can You Create a View with Data from Multiple Tables?
  11. Can You Create a View using Data from Another View?
  12. What Happens If You Delete a Table That Is Used by a View?
  13. Can You Use ORDER BY When Defining a View?
  14. How To Modify the Underlying Query of an Existing View?
  15. Can You Insert Data into a View?
  16. Can You Update Data in a View?
  17. Can You Delete Data from a View?
  18. How To Assign New Column Names in a View?
  19. How Column Data Types Are Determined in a View?
  20. How To Bind a View to the Schema of the Underlying Tables?
  21. How To Create an Index on a View?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...