Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Views and changes Expand / Collapse
Author
Message
Posted Monday, July 2, 2012 6:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:07 AM
Points: 1,765, Visits: 2,139
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
Post #1323758
Posted Monday, July 2, 2012 7:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 1,828, Visits: 1,362
Great easy question this morning Steve. Thanks for that pick me up on Monday.



Everything is awesome!
Post #1323777
Posted Monday, July 2, 2012 8:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 3,261, Visits: 1,955
Thanks for the easy one Steve! I think I have used this command maybe once before.
Post #1323823
Posted Monday, July 2, 2012 9:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
EZ PZ



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1323855
Posted Monday, July 2, 2012 10:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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. :)

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
Post #1323885
Posted Monday, July 2, 2012 11:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 1,297, Visits: 1,657
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.
Post #1323945
Posted Monday, July 2, 2012 11:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:22 AM
Points: 419, Visits: 977
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.
Post #1323963
Posted Monday, July 2, 2012 4:39 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:53 AM
Points: 4,386, Visits: 3,396
A nice one to start the week. Thanks, Steve!
Post #1324069
Posted Monday, July 2, 2012 9:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:19 AM
Points: 1,282, Visits: 1,114
Thanks Steve for the easy one !!!!
Post #1324118
Posted Tuesday, July 3, 2012 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
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.

Nice question, Steve. Thanks!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1324155
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse