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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2703 Visits: 2255
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

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



Everything is awesome!
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5042 Visits: 2371
Thanks for the easy one Steve! I think I have used this command maybe once before.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31932 Visits: 18551
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

mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4792 Visits: 72518
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2578 Visits: 2827
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
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 1181
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
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7288 Visits: 4857
A nice one to start the week. Thanks, Steve!
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1669 Visits: 1218
Thanks Steve for the easy one !!!!
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10829 Visits: 11967
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 MVP
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