Get the code from a System View

Question

Post reply

Get the code from a System View

Jeff Moden

SSC Guru

Points: 1004434
More actions
April 1, 2008 at 6:57 pm

#188103

Believe it or not, I'm still a newbie when it comes to SQL Server 2k5. In 2k, you could create the code for any system object by right clicking on the object in QA and selecting the "Script object to new window as...".
I remember someone telling me how to do it for System Views and Stored procs in 2k5 but, apparently, those particular brain cells are no longer functioning 😛 How do you script a System View or a System Stored Procedure?
Thanks folks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Scripting system stored procedures still works the same.

As for system views, well their text is still in sys.syscomments

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

sys.syscomments

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Thanks guys... I knew it had to be something simple :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

And you can use sp_helptext to get their definition as well.

EXEC sp_helptext 'sys.objects'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Is this the Jeff Moden we know of ? 😉

"Keep Trying"

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Heh... nope... different guy :hehe: ... the other one knew a lot more about the system... this one is on a steep learning curve for 2k5 and has seen the value of asking a question here and there 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 7

There's a section in BOL that maps the various 2k system tables to 2k5 system views.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a616fce9-b4c1-49da-87a7-9d6f74911d8f.htm

I've found it very useful.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

Great link... thanks, Gus!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Hey! Wait a minute...

This isn't homework is it?!?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]