the view. How can I rename a view without disturbing existing permissions?
thanks,
GThis is a multi-part message in MIME format.
--=_NextPart_000_0240_01C3C884.D985F8E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Check out sp_rename in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gary" <gb@.nospam.com> wrote in message
news:eb#ZuwKyDHA.2452@.tk2msftngp13.phx.gbl...
I learned, the hard way, that renaming a view in E.M. doesn't really rename
the view. How can I rename a view without disturbing existing permissions?
thanks,
G
--=_NextPart_000_0240_01C3C884.D985F8E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Check out sp_rename in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Gary"
--=_NextPart_000_0240_01C3C884.D985F8E0--|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As our MVP, TOM said, the system sp_rename can change the name of the
view. You can refer to the Books Online for detailed information. The
example code are as follows:
use pubs
go
create view a
as select * from authors
go
exec sp_rename a, b
select * from b
If you still have questions, please feel free to post new message here and
I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong
Yes , you are right.
But how do you explain that when I went to EM (after running your script)
selected the desire view and when I opened it by right click on the
properties I still see CREATE VIEW a and NOT b.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:xCThiKPyDHA.2604@.cpmsftngxa07.phx.gbl...
> Hi Gary,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> As our MVP, TOM said, the system sp_rename can change the name of the
> view. You can refer to the Books Online for detailed information. The
> example code are as follows:
> use pubs
> go
> create view a
> as select * from authors
> go
> exec sp_rename a, b
> select * from b
> If you still have questions, please feel free to post new message here and
> I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Your prudential is really impressive. You even notice some detailed
information of our pruduct. Here is my explanation to these.
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
use pubs
SELECT text FROM syscomments WHERE id = object_id('b')
you will get 'create view a as select * from authors'.
However, this is just a comment of this object, that is view 'b' , the
object name is saved in sysobjects and the definition of it is 'select *
from authors'. When using the 'sp_rename', it will not change the content
of the syscomments. This will not affect any application of this view. It
just because to keep the high performance of the system SPs.
If you still have questions, please feel free to post new message here and
I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql
No comments:
Post a Comment