CODEWITHSUNDEEP
sql-server
Elliot Rodriguez
Elliot Rodriguez

Reputation: 618

Using Dots In SQL Server Object Names

Before you read - this is not about using the dot (this is a legacy issue and someone else wrongly chose to use a dot in the object name). This is a question about the behavior of SQL Server in this particular (bad) situation.

Our database has two views:

viewOne and viewOne.bkp

Yes, someone stupidly added a dot in the view name.

An automated script that was generated for these objects from a third party tool had a couple of sp_refreshview statements for each of these views.

EXEC sp_refreshview N'[ourSchema].[viewOne]'
EXEC sp_refreshview N'[ourSchema].[viewOne.bkp]'

When run in the order above, the contents of viewOne are overwritten by the contents of viewOne.bkp.

Again, given that dots in object names are NOT best practice, and that MS accepts this as a "valid object name", why would the contents of the former be overwritten by the latter if the schema is defined in both statements AND there are two distinct objects present?

Upvotes: 1

Views: 674

Answers (1)

SqlZim
SqlZim

Reputation: 38023

I am unable to reproduce your issue. Can you provide code that reproduces the issue?

rextester: http://rextester.com/ZPXB17514

/* does sp_refreshview overwrite view definitions? */
create schema ourSchema authorization dbo;
go
create view ourSchema.viewOne as select * from pilots;
go
create view ourSchema.[viewOne.bkp] as select * from flights;
go
select * from ourSchema.ViewOne
select * from ourSchema.[ViewOne.bkp]
go
alter table dbo.pilots  add new int not null default 1;
alter table dbo.flights add new int not null default 1;
go
/* before refresh, doesn't show new columns */
select * from ourSchema.[ViewOne] 
select * from ourSchema.[ViewOne.bkp]
go
EXEC sp_refreshview N'[ourSchema].[viewOne]'
EXEC sp_refreshview N'[ourSchema].[viewOne.bkp]'
go
/* after refresh, shows new columns -- no view overwritten */
select * from ourSchema.[ViewOne]
select * from ourSchema.[ViewOne.bkp]
go

Upvotes: 1

Related Questions