is it possible to drop a table which is referenced in a view
-
There is a possibility to drop a table which is being refered by a view. i think its logically wrong.
Is there any other suggestions
-
Create the view WITH SCHEMABINDING
That way the tables (or other views) referenced by the view cannot be modified in any way unless the view is dropped first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is possible, and does not warn.
use pubs
Select *
into Authors2
from authors
Create view AuthorBooks
as
select au_lname, au_fname, b.title_id, title, type
from authors2 a
join titleAuthor b on a.au_id = b.au_id
join titles c on c.title_id = b.title_id
select top 10 *
from AuthorBooks
Drop Table authors2
select top 10 *
from AuthorBooks
-- Results
-- Server: Msg 208, Level 16, State 1, Procedure AuthorBooks, Line 3
-- Invalid object name 'authors2'.
-- Server: Msg 4413, Level 16, State 1, Line 1
-- Could not use view or function 'AuthorBooks' because of binding errors.
Drop view AuthorBooks
As mentioned create view with schemabinding to prevent this.
-
>> Create the view WITH SCHEMABINDING
That way the tables (or other views) referenced by the view cannot be modified in any way unless the view is dropped first. <<
That is PARTIALLY correct. You CAN modify the table as long as you don't touch the columns included in the view. The other columns are changeable...
been there done that
* Noel -
Ray is right. Table can be dropped. The view that referenced this table still exists but unviewable.
-
> You CAN modify the table as long as you don't touch the columns included
> in the view. The other columns are changeable...
Ah. *reads fine print in BoL* So noted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gila, you're totally correct! but the table still can be dropped before the view
-
???
Create Table Testing (
Test int
)
GO
Create View vwTest WITH SCHEMABINDING AS
Select Test FROM dbo.Testing
GO
DROP TABLE Testing
GO
The drop table returns
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'Testing' because it is being referenced by object 'vwTest'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gila, you got me there!!!
Setting schemabinding will not allow you to drop the table first. Thanks for showing me that!!
I learn new things very day
But if you "REALLY" want to drop your table first (which you really don't want to do) there is still a way.
ALTER View vwTest AS
Select Test FROM dbo.Testing
GO
drop table dbo.Testing
GO
-
Sure. By altering the view like that you've removed the schemabinding, hence you can now do anything with the base table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply