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 which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
102 Comments. Leave new
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.