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.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 0x280030002900 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:
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.
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
We can also use sys.views to list all views.
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