SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views and changes


Views and changes

Author
Message
Thomas Abraham
Thomas Abraham
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6075 Visits: 2256
Bob Razumich (7/2/2012)
For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway. Plus, I'd be strung up if I created a view as Select * from anything. Glad I researched it.

But I have to say that the command would seem to enable poor coding practice in creating a view, IMHO.


It's like Bob was reading my mind! (Except for the fact that he wrote this BEFORE I saw the question. How DO you do that Bob?)

So, +1, and thanks for a great question for a Monday Steve.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Dana Medley
Dana Medley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4314 Visits: 1707
Great easy question this morning Steve. Thanks for that pick me up on Monday. :-)



Everything is awesome!
Ken Wymore
Ken Wymore
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9656 Visits: 2470
Thanks for the easy one Steve! I think I have used this command maybe once before.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145609 Visits: 18652
EZ PZ

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Learn Extended Events

mtassin
mtassin
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13360 Visits: 72528
steve.casey (7/2/2012)
Some of us have to work with versions older than SS 2005, you know! DROP...CREATE is the only one that will work with ALL versions.



That's what I thought too... of course I don't write many views.... tables yes... stored procs yes...... views.... rarely.

That said, I had to find out what the heck sp_refreshview was if it existed... and it changed my answer. Smile

Yay +1



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
sknox
sknox
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6985 Visits: 3161
Bob Razumich (7/2/2012)
For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway...


+1

I figured the first answer was wrong because DROP...CREATE VIEW is not the only way to fix that problem, even without the existence of sp_refreshview. You can also use an ALTER VIEW statement. Since the third answer was patently untrue as well, whatever remained, however improbable, had to be true.

That's how I deduced that there was, indeed, a stored procedure called sp_refreshview.
Bob Razumich
Bob Razumich
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1277
sknox (7/2/2012)
Bob Razumich (7/2/2012)
For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway...


+1

I figured the first answer was wrong because DROP...CREATE VIEW is not the only way to fix that problem, even without the existence of sp_refreshview. You can also use an ALTER VIEW statement. Since the third answer was patently untrue as well, whatever remained, however improbable, had to be true.

That's how I deduced that there was, indeed, a stored procedure called sp_refreshview.


Awesome deduction! Much more entertaining than the method I used.
Revenant
Revenant
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22605 Visits: 5137
A nice one to start the week. Thanks, Steve!
baabhu
baabhu
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4141 Visits: 1218
Thanks Steve for the easy one !!!!
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34622 Visits: 13124
It's a bit disappointing that nobody has yet commented that the "best" way to deal with the issue is to drop the view, then create a new one that does not use SELECT * but spells out the relevant columns. :-D

Nice question, Steve. Thanks!


Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search