CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlview
Relativity
Relativity

Reputation: 6868

Can I replace table with a view (of same name).?

I have 2 databases....a & b

I have table "t" in both of these databases.

Now I am deleting table t from database "b".

I create a view "t" (see that name of view is same as deleted table) in database "b"...and this view referring the table "t" in database "a".

I have a dotnet application..which point to database "b". It has inline queries.... So can I leave the reference like this "b.t" in inline queries. I mean now will it refer view "t" instead of table "t" ?

Upvotes: 3

Views: 7165

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332581

Yes, but the table you're looking to replace has to either be dropped or renamed first -- only one object can have the name.

Use:

CREATE VIEW b.dbo.t AS
  SELECT a.*
    FROM a.dbo.t a

The only caveat is users in the B datbase might need to be granted SELECT privilege:

GRANT SELECT ON b.dbo.t TO user

Ideally, create a role, then grant SELECT to the role which you can then include add to users.

Upvotes: 5

Related Questions