Edit/View a DDL

Question

Edit/View a DDL

Viewing 8 posts - 16 through 22 (of 22 total)

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

John Waclawski SSCommitted Points: 1505 More actions · Answer 1

I have to disagree with you on "clueless developers". The program, even the lite version, has been instrumental on helping not only me but many others not only learn SQL but it works on any database that can create a ODBC connection, but that is not the issue here.

As for your example, you took the easy way out. I was able to do the same with no issues. Try this on for size & tell me if you can "Edit/Design/Open View"

Go to Adventureworks -> Views -> System Views -> Right click on "INFORMATION_SCHEMA.DOMAINS" and tell me what you get. Chances are you'll get "Reports/Rename/Refresh/Properties".

Not that I want to edit or modify any of those objects...I simply want to view them. That's it. End of story. I want to see how they were built just so I can learn from them. Just like a mechanic that wants to know how an engine runs, he takes it apart to see how it works.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Gift Peddie (12/1/2009)
Lynn Pettis (12/1/2009)
Gift, he is trying to look at the code behind the system views which is not possible by right clicking on the view in the Object Explorer in SSMS.
That was what I thought but the last post is using AdventureWorks.
USE AdventureWorks
GO
Select definition
From sys.system_sql_modules
where object_id = OBJECT_ID('INFORMATION_SCHEMA.CHECK_CONSTRAINTS')
--Where INFORMATION_SCHEMA.CHECK_CONSTRAINTS is the object you want the definition on.
2.
USE AdventureWorks
GO

True, he is using AdventureWorks, but look at what he is trying to look at:

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

This is a system view.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Go to Adventureworks -> Views -> System Views -> Right click on "INFORMATION_SCHEMA.DOMAINS" and tell me what you get. Chances are you'll get "Reports/Rename/Refresh/Properties".

The point I am trying to get across is there is no such definition in SQL Server the Information Schema views are in the Master and not in AdventureWorks.

And yes you cannot read them directly because developers were using them to write employer applications and creating memory leaks in SQL Server 2000 and below.

Kind regards,
Gift Peddie

John Waclawski SSCommitted Points: 1505 More actions · Answer 4

Lynn Pettis (12/1/2009)
Gift, he is trying to look at the code behind the system views which is not possible by right clicking on the view in the Object Explorer in SSMS.

You hit the nail on the head, Lynn. I may be a newb at a lot of this, but I always exhaust my own right-clicks, help file, google and co-horts here at work. This was just a thorn in my side & I knew there had to be an answer to it. I'm not sure why Gift is treating my workaround like the plague or assuming I didn't try what he suggested already. Even though this is the "SQL Server Newbies" section, one cannot assume they are idiots. SQL Server is not for the faint at heart. If your here, asking legitimate questions, you have a good head on your shoulders. Respect even the newbs. 🙂

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

John Waclawski (12/1/2009)
Lynn Pettis (12/1/2009)
Gift, he is trying to look at the code behind the system views which is not possible by right clicking on the view in the Object Explorer in SSMS.
You hit the nail on the head, Lynn. I may be a newb at a lot of this, but I always exhaust my own right-clicks, help file, google and co-horts here at work. This was just a thorn in my side & I knew there had to be an answer to it. I'm not sure why Gift is treating my workaround like the plague or assuming I didn't try what he suggested already. Even though this is the "SQL Server Newbies" section, one cannot assume they are idiots. SQL Server is not for the faint at heart. If your here, asking legitimate questions, you have a good head on your shoulders. Respect even the newbs. 🙂

I highly respect the newbie but I also want you to know the risks of using tools that let you access system components you don't understand. I have used SQL Server for years and have not had reason to use anything in the Master and had to edit it only once.

Kind regards,
Gift Peddie

John Waclawski SSCommitted Points: 1505 More actions · Answer 6

I highly respect the newbie but I also want you to know the risks of using tools that let you access system components you don't understand. I have used SQL Server for years and have not had reason to use anything in the Master and had to edit it only once.

Gift, when you put it that way it makes a lot of sense. Your right, I don't understand those particular components. But I didn't know that at the time. I was just looking for an answer to something that was driving me crazy. I was able to view the code to 2 other views that led me to a 3rd one that I couldn't access & that was the thorn in my side. I knew if I could access 2 of them, I SHOULD BE ABLE to access the 3rd one. Little did I know what I was in for.

Thank you for quickly responding to my messages even if they weren't really what I was looking for. I sincerely do appreciate that. It sort of pushed me to figure it out & "prove you wrong" just because I knew it could be done. 🙂

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

An easy way to view the definition of views, procs, etc. is "EXEC sp_helptext 'schema.object' ". I tested it on an INFORMATION_SCHEMA view from a user database, just to be sure it worked.

John Waclawski SSCommitted Points: 1505 More actions · Answer 8

Whoa, Scott!!

I like your solution much better! It gives a nice little multi-line reply to the object your looking at.

Thanks!!