![]() ![]() Now let’s view the text using the sp_helptext system stored procedure as shown below. SELECT * FROM SYSCOMMENTS WHERE ID = OBJECT_ID(‘sp_GetEmployeeDetailsById’) Have a look at the text column of this table which actually stores the text of our stored procedure. WHERE ID = let’s view the text using the SYSCOMMENTS table as shown below. SELECT Name, Gender, CAST(DOB AS DATE) AS DOB Now let’s create one Stored Procedure without using the With Encryption attribute - Stored Procedure without with encryption option Explicit recompilation eliminates the need for implicit run-time. INSERT INTO Employee VALUES(1, 'Pranaya', 'Male',' 10:53:27.060', 1) Use the ALTER PROCEDURE statement to explicitly recompile a standalone stored procedure. Populate the Employee Table with test data Please use the below SQL Script to create and populate the Employee table - Create Employee Table The Sp_depends system-defined stored procedure can also be used with other database objects like table, view, etc. This procedure is very useful when we want to check if there are any stored procedures that are referencing a table that we are going to drop. ![]() The s p_depends system-defined stored procedure is used when we want to see the dependency object of a stored procedure. The sp_helptext system procedure will retrieve the information from the text column of the syscomments table and then displays it. In the above syscomments table, there is a column called “text” which actually stores the complete create procedure or function statement. The most important point to keep in mind is that whenever we created a stored procedure or function then the content or text of the function or procedure is going to be saved under the syscomments system table Example: This sp_helptext system procedure is used when you want to view the text of a subprogram such as function and procedures. You can also press the ALT+F1 key to get the information when the name of the object is highlighted. The Sp_help stored procedure not only used to give information about a stored procedure but also give information about other database objects like tables, views, triggers, etc. This sp_help system-defined stored procedure is used to view the information of a stored procedure like parameter names, their data type, etc. Let’s have a look at some of the useful system-defined procedures that are very important while working with SQL Server. Learning Some useful system-defined stored Procedure in SQL Server. Understanding the With Recompile Attribute in SQL Server Stored Procedure.Understanding the With Encryption Attribute in SQL Server Stored Procedure.Why do we need to encrypt the text of a stored procedure?.How to encrypt the text of a stored procedure in SQL Server?.How to view the text of a stored procedure?.Learning Some useful system-defined stored Procedure in SQL Server.Data Structures and Algorithms Tutorials. ![]()
0 Comments
Leave a Reply. |