Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
View Options
Not every query may be turned into a view. There are rules which must be followed before your queries may be turned into views.
View Rules
This query includes a simple aggregation which totals the grant amounts according to each EmpID. It’s a handy report, but we can’t turn it into a view. The error message shown displays when you attempt to run this code and create the view. Notice that it says “…no column name was specified for column 2.”
We must first make certain this expression field column has a name before we can create this view. Alias the expression field as “TotalAmount” and then run this CREATE VIEW statement for vEmpGrantTotals.
CREATE VIEW dbo.vEmpGrantTotals
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID
Encrypting Views
Suppose you want to make sure that people can utilize this view to run reports, but you don’t want them to be capable of seeing or recreating the underlying code. The sp_HelpText system stored procedure reveals the code which created an object.
We want to alter this view so that the source code is encrypted. Two modifications to the code for vEmpGrantTotals will make this change:
1) Change CREATE VIEW to ALTER VIEW.
2) Add WITH ENCRYPTION before the AS keyword.
ALTER VIEW dbo.vEmpGrantTotals
WITH ENCRYPTION
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID
The best practice after we create or alter an object is to run a SELECT statement to confirm that it produces the expected result. Look at Object Explorer and notice that a small padlock now appears on the icon for vEmpGrantTotals.
Often times you can just right-click a view in Object Explorer and choose “Script View as” to see the code for the view. Now when we attempt that maneuver for our encrypted view, SSMS gives us a message saying that the text is encrypted and we can’t script this view. Management Studio (SSMS) will not allow us to generate code for the encrypted view. The properties dialog for vEmpGrantTotals also tells us that the view is now encrypted.
Attempt to run the sp_HelpText sproc and notice the message, “The text for object ‘dbo.vEmpGrantTotals’ is encrypted.”.
sp_helptext 'dbo.vEmpGrantTotals'
Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object ‘dbo.vEmpGrantTotals’ does not exist in database ‘JProCo’ or is invalid for this operation.
Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter4.2Setup.sql script from Volume 4.
Question 23
What are the two ways to see the code that created a view? (Choose Two)
- WITH SCHEMABINDING
- WITH ENCRYPTION
- sp_helptext
- sp_depends
- sys.syscomments
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Hi,
The options given do not seem to fit, so I’ll go ahead and give you answers from your post. The two ways to view the code that created a view are:
1. Run a SCRIPT VIEW AS –> CREATE TO command
2. Run the system stored procedure sp_helptext on the view with the syntax sp_helptext ” . Based on the scenario, we can choose to include or exclude the schema prefix.
Ramakrishnan RS
Mysore, India
Options were correct but I mixed up wrong question.
Everything is fixed. Many thanks for pointing out this error.
Q.No 23 : What are two ways to see the code that created a view? (Choose two)
1) Using sp_helptext
2) In Object explorer, Right click a view and choose “Script View as” to see the code for the view.
Note: i think today Quiz answers are not related to question. Its same like as yesterday Quiz. please, review it.
Chennai, Tamilnadu, India
Hi Pinal ,
Are the options correct ?
Nagaraj
Options were correct but I mixed up wrong question.
Everything is fixed. Many thanks for pointing out this error.
Hi Pinal,
Looks like questions & answers of Day 22 & 23 are mixed up. Please fix it.
Thanks
Sudhir
Hi All,
I have fixed question of this quiz. Due to a small mix up different question was posted here but now everything is fixed. Please look at that and see if you can win book 4:)
Hi Pinal,
This should be « SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – All about SQL Constraints – Day 23 of 35 whereas its marked as 22.
Topic is View whereas Question is on constraint. Please take a look & fix this.
Thanks
Sudhir
Fixed it – thanks Sudhir for pointing out this error.
Pinal,
I couldn’t see the winners list at all from the link that you have given. That fb link just shows all te list of ur blog posts and if I click that it’s coming to ur blog. Where can I see the winners list and the correct answers for each day’s question
Sathya
Just like the page and you will be able to see all the details.
Still Questions for both Day 22 & 23 are same. Please see.
Sudhir – they are fixed. It may be temporary browser cache. Please refresh with CTRL +F5.
Once again I really thank you for pointing this out.
Hi,
Option 3 & 5 are correct as sp_helpText returns the text unless its encrypted. and sys.syscomments.
for example
select text from sys.syscomments a
inner join sys.objects b on a.id = b.object_id
where b.name = ‘viewname’ –view is the name of the view
sp_helptext ‘viewname’
Thanks
Sudhir Chawla
New Delhi, INDIA
If the view is not encrypted, you can see its definition using sp_helptext SP or sys.syscomments.
So, the correct options are 3 and 5.
I am from USA
Correct answers are:
* Use sp_helptext ‘dbo.viewName’
* right-click a view in Object Explorer and choose “Script View as”
Rene Castro
El Salvador
I think the title of the Day is wrong. The post is about views, not about constraints. The content of the page and questions seem to be OK, but the title doesn’t look correct to me.
Also, this text didn’t mention sys.syscomments, so I just ran a query
select * from sys.syscomments to make sure it included the view definition.
Naomi,
You are correct fixed it – it just mixed up with earlier blog post. I can see why this mistake happened and have fixed the issue. You are correct.
Only option 3 is correct. Other option is to use the Ui to generate the script using ssms. Right click the view and click Create script to new query editor window
Sathya
India
Answer is correct=3)sp_helptext
krishan kumar mishra
India
Hello PInal,
The correct answers are;
3. sp_helptext
5. sys.syscomments
Bulent
Answer For Q.23 are 3 & 5
1) Using sp_helptext
2) In Object explorer, Right click a view and choose “Script View as” to see the code for the view.
Question 23
Ans : sp_helptext
Chennai, INDIA
Q.No 23 : What are two ways to see the code that created a view? (Choose two)
1) Using sp_helptext ‘vEmpGrantTotals’
should not use scheema.viewname.
2) In Object explorer –> database –> Views–> Right click on view and choose “Script View as” to see the code for the view.
Raghunath.G.R
Bangalore, India
Hi,
sp_helptext displays the view definition.
In addition,
sys.syscomments itself is a view.
sp_helptext ‘sys.syscomments’
India
If not use Encryption in view
we can see code using sp_helptext
WITH SCHEMABINDING is used to binds a view to the schema of the tables.
WITH ENCRYPTION used to encrypt a view or sps.
sp_depends := Displays information about database object dependencies, such as the views and procedures that depend on a table or view.
sys.syscomments := Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database.
So Only one option is correct ie., option 3. sp_helptext
Nikhildas
Cochin
INDIA
Hi ,
Correct Option is 2 and 3.
I am from Gujarat,India
[...] Q 23) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23… [...]
The Correct Options are
==================
3.sp_helptext
5.sys.syscomments
Explanation:
sp_helptext :
We can use this to display the the definition of a View.
sp_helptext
sys.syscomments :
Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.sysobjects.NAME = ”
AND sys.sysobjects.type = ‘V’
sys.sysobjects.name – displays the View Name
sys.syscomments.text – displays the definition of the View
[To get the id of the view object - we join sys.syscomments with the sys.sysobjects]
Country :
=======
India
Correct Answer is 3 & 5.
3. sp_helptext
5. sys.syscomments
sp_helptext: We can use this to display the the definition of a View.
sys.syscomments: Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
sys.sysobjects.name - Displays the View Name.
sys.syscomments.text – Displays the definition of the View.
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India
Correct answer to this question is options
3 sp_helptext and
5 sys.syscomments
With sp_helptext passing object name like view,procedure,function etc we can see its defination. With using sys.syscomments you can make a query on text column.
Mahmad Khoja
INDIA
AHMEDABAD
Assumption:
That the view is not created with encryption key word:
The two answers are:
3.sp_helptext
and
5.sys.syscomments
sys.syscomments Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
Sonnie Avens
New York, USA
Correct options are 3 & 5.
3.sp_helptext
sp_helptext ‘viewname’
5. sys.syscomments
SELECT A.text,object_name(A.id)
FROM sys.syscomments A
WHERE A.id = object_ID(‘viewname’)
New Delhi
India
The correct answers are:
3. sp_helptext
5. sys.syscomments
City: Baroda
Country: India
Thanks
GurjitSingh
Answers
3. sp_helptext
5. sys.syscomments
Country : India
Ans: 3 And 5 both are correct
3.sp_helptext ‘viewName’
5.select [text] from sys.syscomments sc
inner join sys.objects so on sc.id = so.object_id
where so.[name] = ‘viewname’
Partha
India
Correct Answer is Option 3 & 5.
3.sp_helptext
5.sys.syscomments
Country:India
Thanks,
Fazal Vahora
The Correct Answer for this Question is Option – 3 & 5
Through Sp_Helptext, Sys.syscomments we can see the code that created a view.
Thanks,
Narendra (India).
Answer
Option 3: sp_helptext
Query: sp_helptext vw_USER_PRIVILEGES
Result:
CREATE VIEW [dbo].[vw_USER_PRIVILEGES]
AS
SELECT DISTINCT U.USER_ID, UPV.PRIVILEGE
FROM dbo.USERS AS U INNER JOIN
dbo.USER_ROLES AS UR ON U.USER_ID = UR.USER_ID INNER JOIN
dbo.USER_PROFILE_DEFINITIONS AS UPD ON UR.PROFILE_ID = UPD.PROFILE_ID INNER JOIN
dbo.USER_PRIVILEGES AS UPV ON UPD.PRIVILEGE_ID = UPV.PRIVILEGE_ID
Option 5: sys.syscomments
It will get all the system object (strored procedure, user defined function and Views).
Query: SELECT * FROM sys.syscomments
Result:
id, number, colid, status ctext, texttype, language, encrypted, compressed, text
155863622 0 1 0 0×280030002900 2 0 0 0 (0)
160055656 0 1 0 0x0D000A004300520045004100540045002000460055004E004300540049004F004E002000640062006F002E00660075006E0063005F005200450050004D0041004E005F004700450054005F0055005300450052005F005200450050004F00520054005F0048004900530054004F005200590020000D000A00280009000D000A00090009004 2 0 0 0 CREATE FUNCTION dbo.func_REPORT_HISTORY ( @USER_ID INT , @NR_OF_ENTRIES INT , @FILTER_DUPLICATES BIT ) RETURNS @GET_USER_REPORT TABLE ( USER_REPORT_HISTORY_ID INT , USER_REPORT_ID INT , USER_REPORT_NAME VARCHAR(128) ) AS BEGIN IF (@FILTER_DUPLICATES = 1) BEGIN SELECT * FROM EMPLOYEES END RETURN END
874486194 0 1 0 0x4300520045004100540045002000560049004500570020005B00640062006F005D002E005B00760077005F0049004E0056004F004900430045005F0041004E0041004C0059005300490053005F00500052004F0044005500430054005F00470052004F00550050005D000D000A00410053000D000A000D000A000900530045004C0045004 2 0 0 0 CREATE VIEW [dbo].[vw_INVOICE_PRODUCT_GROUP] AS SELECT TOP (100) PERCENT ORGANIZATION_NAME AS ORGANIZATION FROM ORGANIZATION ORDER BY ORGANIZATION_NAME
Devarajan (India)
Hi,
Pinal Option 3 and 5 are right Answers. (However given details are not sufficient to point out towards answers)
Answers are:
3) sp_helptext ‘dbo.viewname’
5) sys.syscomments
Others
## Right click a view in Object Explorer and choose”Script View as” to see the code that created a view.
More detail is here:
http://www.sqlservercurry.com/2010/09/display-view-definition-using-t-sql.html
Thanks…
Rajneesh Verma
(INDIA)
Correct Answer is Option 3 & 5.
3.sp_helptext
5.sys.syscomments
Regards
Rajesh
From India
Option # 3 which is SP_HelpText would show you if it is not encrypted so option #3 would be right answer.
At the same time, if you want to see TSQL script for the view or SPs etc. you can use Sys.SysComments too.
here is the script for the same from my blogpost.
http://www.sqlhub.com/2009/05/find-specific-word-or-phrase-from-all.html
SELECT DISTINCT so.name,sc.text
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.xtype in (‘V’) and sc.TEXT LIKE ‘%YourViewName%’
order by name
this will not guarantee of one results, if you have call your view in another View, it will return that too but SP_HelpText would guarantee you the perfect matching one result only.
my answer for the questions is
3. sp_helptext
5.sys.syscomments
the explanation for both option is as follows.
3. sp_helptext —-
CREATE VIEW authorlist
(AuthorName, Location)
AS
SELECT SUBSTRING(au_fname + ‘ ‘ + au_lname,1,25),
SUBSTRING(city + ‘, ‘ + state + ‘ ‘ + zip,1,25)
FROM authors
GO
SELECT * FROM authorlist
–
– see your views:
SELECT name, crdate
FROM sysobjects
WHERE type = ‘V’
– to see your source code for the view
EXECUTE sp_helptext AuthorList
execute this code, u will get the information
5.sys.syscomments
The actual code for views, rules, defaults, triggers, CHECK constraints, DEFAULT constraints and stored procedures are stored in the syscomments table. The column TEXT in the syscomments table contains the actual code for all these objects.
select TEXT from syscomments
Where TEXT like ‘%author%’
execute this and will get information
Thanks & Regards,
Ragini Gupta,
India
Answer is Option 3 & 5.
3.sp_helptext
5.sys.syscomments
Regards
kulwant
From India
Hi Sir,
The correct options are 3 and 5
For the example shown in the above article.
3. if we run the sp_helptext ‘dbo.vEmpGrantTotals’ we get the code that created a view.
5. sys.syscomments should be used with a join with sys.sysobjects to get the code that created a view.
the query is :
SELECT [text] FROM sys.syscomments AS a
JOIN sys.sysobjects AS b ON b.id = a.id
WHERE b.name = ‘vEmpGrantTotals’
Thanks and Regards,
P.Anish Shenoy,
INDIA,Bangalore, Karnataka.
Correct Options are
3.sp_helptext
5.sys.syscomments
I’m from india
is this book of yours on sale? hmmm…thanks
Valid options are 3 and 5:-
3.sp_helptext
sp_helptext ‘viewname’
5. sys.syscomments
SELECT A.text,object_name(A.id)
FROM sys.syscomments A
WHERE A.id = object_ID(‘viewname’)
Bangalore
India
Hi Pinal,
The correct options are 3 and 5
3. sp_helptext Displays the definition of a user-defined view or system object such as a system stored procedure.This gives the code thar created the view.
5. sys.syscomments Contains entries for each view,stored procedure within the database. This should be used with a join with sys.sysobjects to get the code that created a view.
Thanks & Regards
Santosh.S
Bangalore, India
At Present, according to me, code that created a view, can be seen using “sp_helptext” and “sys.syscomments”
sp_helptext displays the definition of a user-defined rule, unencrypted Transact-SQL stored procedure/ view, user-defined Transact-SQL function, trigger, computed column, CHECK constraint,or system stored procedure.
Where as,
sys.syscomments contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. In SQL server 2008, its mapped as “sys.sql_modules”
Syntax for sp_helptext is as follows,
USE [database_name];
GO
EXEC sp_helptext ‘[view_name]‘;
GO
Syntax for sys.sql_modules is as follows,
USE [database_name];
GO
SELECT sm.object_id
FROM sys.sql_modules AS sm
GO
Other than the options given above, one can also see the code by right clicking the object explorer and selecting ‘script view’.
COUNTRY – INDIA
The Correct Option is
# 3. I didn’t find second one. Pinal please tell me second one.
Country : India
Correct Options are
3.sp_helptext
5.sys.syscomments
CREATE VIEW dbo.vEmpGrantTotals
AS
SELECT EmpID, SUM(Amount) AS TotalAmount
FROM [Grant]
GROUP BY EmpID
For sp_helptext
=============
sp_helptext vEmpGrantTotals
For sys.syscomments
=================
SELECT DISTINCT c.text
FROM syscomments c
inner JOIN sysobjects o ON
c.[id] = o.[id]
WHERE o.[name] = ‘vEmpGrantTotals’ AND o.TYPE =’V’
Thanks,
Mitesh Modi
Gujarat, India
3 and 5 are the correct answers.
Sreeram
India
Hi Pinal,
Correct Options are 3 & 5.
3. sp_helptext
5. sys.syscomments
Thanks & Regards
Neeraj Bansal
Noida
Correct Answer option No : 2 & 3
Shekhar Gurav.
Country : India
Option 3 & 5 are correct
These two options, sp_helpText and sys.syscomments returns the text unless its encrypted.
(Sale, Nigeria)
The correct Answers are :3.sp_helptext, 5. sys.syscomments
3.sp_helptext ‘[view_name]‘
there is no easy way to use the result of this query.
5. sys.syscomments
The disadvantage here is that this method is lengthy and you have to specify the object name and schema name separately.
http://bit.ly/oeZBJv
Richardson, Texas, USA
Correct Answer is option 3 & 5.
3. sp_helptext
5. sys.syscomments
sp_helptext: We can use this to display the the definition of a View.
sys.syscomments: Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
sys.sysobjects.name – Displays the View Name.
sys.syscomments.text – Displays the definition of the View.
Malay Shah
Ahmedabad, India
Option 3 and 5 are correct
Robin Thomas
India
Something must have gone wacky with this email: It has the same title as yesterdays email (Day 22 of 35 not Day 23 of 35), this question is about View Options, at the bottom of the email it shows Question 23 along with a question about views (What are two ways to see the code that created a view? (Choose two)), but the options to choose from came from the Check Constraint question from day 22 of 35.
Since I have no real options to choose from I will also choose options 3 & 5
USA
Mike Michalicek
Answers 3 and 5 will work.
You can also use sys.sql_modules with code to the effect of:
SELECT definition
FROM sys.sql_modules
WHERE object_id = Object_id(”)
Matt Nelson, USA
Options 3 & 5.
Reagrds,
Girish Rokade
Pune India
The Correct answers are option 3 & 5.
Examples,
We we take view1 as a view definition.
then,
sp_helptext View1
SELECT text FROM sys.syscomments WHERE id = OBJECT_ID(‘View1′);
Regards,
Girish Rokade
Pune India
Answers are 3 and 5.
Additionally you can also use “Script View as” which is what I’ve typically done. However, none of these methods will work once encryption is turned on. It is a way to protect others from viewing the code.
USA
The correct answers are options 3 and 5 to view the source code of a non-encrypted view.
Country: United States
Hi Pinal,
Challenge:
What are the two ways to see the code that created a view? (Choose Two)
1.WITH SCHEMABINDING
2.WITH ENCRYPTION
3.sp_helptext
4.sp_depends
5.sys.syscomments
Correct Answer with Explanation:
The correct choices listed abobe to see the code that created a view are #3 and #5: using sp_helptext and by running a query against sys.syscomments. The view cannot be encrypted, though.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Hi everyone
The answer is 1 and 5
sp_helptext
or
select text from sys.siscomments where text like ‘%<view_name%'
from: Chile
Answer to the above question is :
3.sp_helptext
5.sys.syscomments
Why option 3 :
Sp_helptext is a system stored procedure, which is used to view the contents of the objects created in SQL Server. Contents of objects such as unencrypted stored procedure, user defined function, view, trigger etc. etc. can be displayed using above mentioned SSP.
To view the contents of view using sp_helptext
sp_helptext
Why option 5 :
sys.syscomments contains information / entry regarding all objects those are getting created in SQL Server. Objects such as sp name, functions, views, check constaints, default constaints, rules, triggers etc. etc. The definition of all the above mentioned objects is stored in Name column of sys.syscomments.
To view the contents of view using sys.syscomments
select name,text from sys.syscomments a
inner join sys.objects b on a.id = b.object_id
where b.name = ‘view_name’ and b.xtype=’V’
Kedar Dighe.
Country of Residence – India
The correct options are 3 and 5.
3)
sp_helptext ‘viewname’
and
5)
select sc.text from sys.syscomments sc
inner join sys.objects so on sc.id = so.object_id
where so.name = ‘view_name’
Country: USA
correct answer is sp_helptext and sys.syscomments
India
3.sp_helptext
5.sys.syscomments
Gordon Kane
Allen TX
USA
3. sp_helptext
5. sys.syscomments
These are two of the ways to see the code that created a view.
David
USA
The correct options are:
3. sp_helptext
5. sys.syscomments
Both of these methods will return the code that created the view.
Country of residence: India
Correct Answer are: 3 & 5.
3. sp_helptext
5. sys.syscomments
sp_helptext: Can be used to display the the definition of a View.
sys.syscomments: It has definition of each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database.
Country: India
3.) sp_helptext
5.) sys.syscomments
3.) sp_helptext
5.) sys.syscomments
USA
Garry Bargsley
Hi Pinal,
The correct answers are Option #3) and Option #5)
For the example shown in the above article.
Option 3)Here when we run the sp_helptext ‘View’ we will have the source code used for actually creating the specified View.
Option 5)Here sys.syscomments is to be used along with a join with sys.sysobjects to get the same code.
Other uses of sys.syscomments
1. “Retrieves the name of stored procedures which consists the text ‘order’ in the definition.”
2. “Retrieves the name of view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedures which consists the text ‘order’ in the comments.”
3. “Retrieves the name of view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedures which consists of the text ‘order’ in the definition.”
Comments are part of a procedure’s definition, so if you retrieve the definiton, you retrieve the comments as well.
CREATE PROCEDURE QOTD_Test
AS
PRINT ‘This is a test’; — this is a comment
GO
SELECT “text” FROM sys.syscomments WHERE id = OBJECT_ID(‘QOTD_Test’);
The result of the SELECT statement contains the “this is a comment” comment
this is also an option.
Diljeet kumari
country : India
3 and 5 options are correct.
Thanks,
Bhakser, USA
following two of the ways to see the code that created a view.
option 3: sp_helptext and
option 5 :sys.syscomments
Somnath Desai
India
The correct options are option 3 and 5. i.e.
3. sp_helptext
5. sys.syscomments
india
The correct answer is option’s 3 & 5
3.sp_helptext
5.sys.syscomments
Cheer’s
Prasad Yangamuni
INDIA (PUNE)
The Correct Answer: Option 3 & 5
Through Sp_Helptext, Sys.syscomments we can see the code that created a view.
Country : USA
Q.No 23 : What are two ways to see the code that created a view? (Choose two)
3) sp_helptext
5) sys.syscomments
—-
sp_helptext
select text from sys.syscomments where id =
(select object_id from sys.objects where name = )
Chennai, Tamilnadu, India
The correct answers for the question “What are the two ways to see the code that created a view” are
Option 3 and 5
sp_helptext and sys.syscomments
Dilip Kumar Jena
country : India
The correct answer is 3 and 5
3) Sp_helptext
5) sys.syscomments
Example:
– creating view
CREATE VIEW MYVIEW
AS
SELECT * FROM SALES
– Displaying the View code with sp_helptext
SP_HELPTEXT MYVIEW
– Displaying the View code with using sys.syscomments
SELECT TEXT FROM SYS.SYSCOMMENTS WHERE ID=OBJECT_ID(‘MYVIEW’)
country INDIA
The correct answers are option 3 & option 5.
sp_helptext
sys.syscomments
Ramdas,USA
Correct Answer is: option 3 & 5
Thanks,
Basavaraj
India
The correct answers are:
3. sp_helptext
5.sys.syscomments
Sudeepta,
India.
Answer is:
3.sp_helptext
5.sys.syscomments
Vinay
Pune,India
What if we encrypted it then i myself want to change the view code ? any way to decrypt it back or else i’ll always need a readable backup copy .
Namaste (regards)!
Anugrah
1) Using sp_helptext ‘vEmpGrantTotals’
should not use scheema.viewname.
2) In Object explorer –> database –> Views–> Right click on view and choose “Script View as” to see the code for the view
Bablu
INDIA
Question no 23.
Correct Answer 3 & 5 [sp_helptext & sys.syscomments]
Description :–
Sys.syscomments :– This is a system view which use other different system objects.
sp_helptext :: is used to show the definition of store procedure and view. If you see the definition of sp_helptext, then you find that this system store procedure is using sys.syscomments system view.
Vinay
Correct Answers are : 3 & 5
Because.,
Sp_helptext:
Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
sys.syscomments:
Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
Raju,
India.
The correct answers are:
3. sp_helptext
5.sys.syscomments
Jitendra
Copenhagen,Denmark
Pinal,
Correct Answers are 3(sp_helptext) & 5(sys.syscomments)
Desc:
sys.syscomments:-
select [text] from syscomments where id = OBJECT_ID(”)
sp_helptext:-
sp_helptext ”
Otherways are:
1. GOTO Object Explorer –> Database –> Views–> Right click on view and select “Script View as” to find the vode of the view
2. select VIEW_DEFINITION from information_schema.VIEWS where TABLE_NAME =”
Location: India
Answers are 3 and 5.
SDDesh,
USA
Answers: Option 3 and 5
If it is not encrypted you can get the code definition from sys.syscomments and/or sp_helptext.
Additionally you can get it from the GUI by right clicking the view and selecting to script it out.
Option 3 and 5 are correct:
3. sp_helptext – this system stored procedure reveals the code which created an object.
5. sys.syscomments – Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
Additionally using SSMS you can right-click on the view and click “script view as” and choose “create to” and then “new query editor window” to see the code used to create the view.
Country of Residence: USA
The correct options are
3.sp_helptext
5.sys.syscomments
Q 23) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23 of 35
A.) 3 and 5
Winner from USA: Naomi
Winner from India: Nakul Vachhrajani
I thank you all for participating here. The permanent record of this update is posted on facebook page.
[...] Q 23) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Views – Day 23… [...]