logo

한국어

Language

View the Definition of a Stored Procedure


Applies To: SQL Server 2016

Security

Permissions

System Stored Procedure: sp_helptext
Requires membership in the public role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.

System Function: OBJECT_DEFINITION
System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. These permissions are implicitly held by members of the db_owner, db_ddladmin, and db_securityadmin fixed database roles.

Object Catalog View: sys.sql_modules
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

You can use one of the following:

Using SQL Server Management Studio

To view the definition a procedure in Object Explorer

  1. In Object Explorer, connect to an instance of Database Engine and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. Expand Stored Procedures, right-click the procedure and then click Script Stored Procedure as, and then click one of the following: Create To, Alter To, or Drop and Create To.

  4. Select New Query Editor Window. This will display the procedure definition.

Using Transact-SQL

To view the definition of a procedure in Query Editor

System Stored Procedure: sp_helptext

  1. In Object Explorer, connect to an instance of the Database Engine.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statement that uses the sp_helptext system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    EXEC sp_helptext N'AdventureWorks2012.dbo.uspLogError';  
    
    

System Function: OBJECT_DEFINITION

  1. In Object Explorer, connect to an instance of the Database Engine.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements that use the OBJECT_DEFINITION system function. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));  
    
    

Object Catalog View: sys.sql_modules

  1. In Object Explorer, connect to an instance of the Database Engine.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements that use the sys.sql_modules catalog view. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    SELECT definition  
    FROM sys.sql_modules  
    WHERE object_id = (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));  
    
    

Create a Stored Procedure
Modify a Stored Procedure
Delete a Stored Procedure
Rename a Stored Procedure
OBJECT_DEFINITION (Transact-SQL)
sys.sql_modules (Transact-SQL)
sp_helptext (Transact-SQL)
OBJECT_ID (Transact-SQL)

조회 수 :
3069
등록일 :
2016.08.03
19:14:26 (*.175.161.172)
엮인글 :
http://www.cubequest.org/xe/index.php?mid=sql&document_srl=165966&act=trackback&key=63d
게시글 주소 :
http://www.cubequest.org/xe/index.php?document_srl=165966
List of Articles
번호 제목 글쓴이 날짜 조회 수
49 [MYSQL]테이블 크기 확인 Cheeryj 2017-05-01 2991
48 [MYSQL]GROUP_CONCAT persona 2016-10-28 2514
47 Get size of all tables in database persona 2016-10-20 24761
46 Enterprise manager에서 한글로 바뀌는 문제 persona 2016-10-20 1270
» View the Definition of a Stored Procedure persona 2016-08-03 3069
44 [MSSQL] 테이블 프로시저 존재 체크 persona 2016-07-28 2320
43 [MSSQL] Collation persona 2016-06-23 3895
42 [MSSQL] RESTORE할 때 오류 persona 2016-06-21 2936
41 [MSSQL] Resetting SQL Server Identity Columns: persona 2016-06-20 1724
40 [MSSQL]using SQLCMD persona 2016-06-05 6372
39 PostgreSQL 설치 & 사용법 정리 persona 2016-05-05 21775
38 [PostgreSQL]설치 persona 2016-04-30 34025
37 [MSSQL] Index 리스트 얻기 persona 2016-04-07 7520
36 MySQL EXPORT & IMPORT [1] persona 2016-02-20 4933
35 MySQL via MERGE table engine: persona 2015-10-15 13436
34 MySQL charset encoding [1] persona 2015-06-12 5408
33 SQL SERVER INSTANACE LIST persona 2015-04-17 4238
32 SQL JOIN file Cheeryj 2015-03-25 4887
31 SQL SERVER SINGLE USER MODE persona 2015-03-17 5570
30 MSSQL FILESTREAM 활성화 persona 2015-03-17 5507