Viewing the created Stored Procedures in sql server

After a stored procedure has been created, you can view the code within the database engine in a few ways.

Ø  SP_HelpText

Ø  sys.sql_modules

Ø  OBJECT_DEFINITION

Read Stored Procedures here: Stored Procedure in SQL server (in depth)

http://bageshkumarbagi-msbi.blogspot.in/2016/06/stored-procedure-in-sql-server-in-depth.html

SP_HelpText

With the help of system stored procedure SP_HelpText we can view the text of stored procedure.

EXECUTE SP_HelpText 'SP name'

See the example

EXECUTE sp_helptext 'uspGetBillOfMaterials'

sys.sql_modules

With the help of sys.sql_modules system view we can see the text of SP

SELECT definition FROM sys.sql_modules WHERE object_id = (OBJECT_ID ( N'uspGetBillOfMaterials')  );

See the output

We can see the definition in note pad ++

OBJECT_DEFINITION

With the of OBJECT_DEFINITION built-in function we can see the SP Text

SELECT OBJECT_DEFINITION (OBJECT_ID ( N'uspGetBillOfMaterials')  );

See the output.

We can see the text in note pad ++