This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.
Syntax
sp_helptext @objname = 'name'
sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name
Displaying the definition of a trigger or stored procedure
sp_helptext 'dbo.nameofsp'
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
71 Comments. Leave new
Hi pinal dave
Sp_helpText is very good option for viewing
the sp’s, Trigger’s , View’s and functions
but try one system table which is more interesting to all experiment persons like me
Syscomments , this Table holds all information of all objects text
I want to see/ display the SP’s definition through the browser,
Is there any function available to display?
How to display all the table in a particular database
select * from information_schema.tables this query returns all the tables in a database.
exactly
Hi,
What permissions are required to use sp_helptext with Views?
– KJ
Hello,
how do you remove the default column (text).
Thanks
KJ
hi
the answer was given a straight forward one,
it was very helpfull to me .
thanks
sai
Hi,
I was looking to display stored proc. code for SQL server. Then landed your blog through Google search.
Excellent and precise information!
Thanks.
Gaurav
Same comments as @Sairani and @gaurav. THANK YOU!
Hi Pinal,
plz can u some important interview questions on backup&recovery in dba
Hello, we lost our formatting (i.e. indentation) when we reterive text using sp_helptext.
Is there any way to get exact formatting?
pls give me the proper code for displaying the records of stored procedures
Thanks – exactly the information I was looking for.
Is it possible to save the output of sp_helptext cmd in text file under a floder?
hi pinal,
can u please provide me whole sql command list such as sp_helptext, or other short cuts.
please list out or give me link.
thanks in advance.
@Ashutosh
In Books Online Search for the word : System Stored Procedures, check all stored procedure and get familiar with definition, sql syntax .
Below are very short scripts that will give you some basic information
/* ================================
Impersonate yourself as other user.
===================================*/
Execute as login = ‘new_login_name’ —
or
Execute as user = ‘new_login_name’
–Example:
select suser_sname()
Execute as login = ‘new_login_name’
select suser_sname()
revert — brings back to your original login/user.
/* ================================
Get the list of all databases
================================ */
select * from sys.databases — SQL Server 2005
Select * from sysdatabases — SQL Server 2000
–Tip:When you are in SQL Server Management Studio or Query Analyzer, Press Ctrl + U, it will activate drop down will all user defined databases.
/* ================================
Get Current Database Name
================================ */
Select Db_Name()
/* ================================
Get list of database files for a specific database
================================ */
Select * from sys.sysfiles — sql server 2005
or
select * from sysfiles — sql server 2000
/* ================================
Get the list of all logins in a server
================================ */
Select * from syslogins — sql server 2005
or
select * from sysxlogins — sql server 2000
/* ================================
Get Current logged in login Name
================================ */
Select suser_sname()
/* ================================
Get the list of all users in a database
================================ */
Select * from Sysusers — sql server 2000
Select * from sys.Sysusers — SQL Server 2005
/* ================================
Get Current logged in User Name
================================*/
Select user_name()
/* ====================================
Get a list of all objects in a database
=======================================*/
Select * from sysobjects — sql server 2000
or
Select * from sys.all_objects — SQL Server 2005
/* ================================
Get list of all user defined tables.
=====================================*/
Select * from information_schema.tables
or
Select * from sys.tables — SQL Server 2005
/* ================================
To get list of all views
================================*/
Select * from sys.views order by create_Date desc — SQL Server 2005
/* ================================
To get list of all procedures,
================================*/
Select * from sys.procedures order by create_Date desc — SQL Server 2005
/*================================
To get list of columns
================================*/
Select * from Information_Schema.columns
/*================================================================
Get the definition (Original text) of procedure, view, function.
================================*================================*/
Exec Sp_Helptext Object_Name
or
Exec Sp_Helptext [owner.objectname]
/*================================================================================================
Get Table ,View structure, information about indexes, constraints, data type, data length.
================================================================================================*/
Exec Sp_Help object_name
or
Exec Sp_Help [owner.object_name]
/*================================================================
Get all dependency on a particular object.
================================================================*/
Sp_depends object_name
/*================================================================
Get list of orphaned (sql server) users.
================================================================*/
Sp_change_users_login ‘report’ :
/* ================================================================
Get Object Level Permision for each user or db_role
================================================================*/
sp_helprotect
or
sp_helprotect @user_name = ‘name of the user’
How can i see my own text as i do it in c by using printf command.
is ther is any command for print text in sql storeprocedure.
@Amit
create proc usp_example1
as
print ‘This is how you print it’
go
Exec usp_example1
go
drop proc usp_example1
~ IM.
hi ,
i have a problem with calling a user defined function from ‘masters’ database which has been defined in ‘user’ database. i am using SQL 2000 server
How can I search all triggers in all databases for the existence of a specific string?
I can select routine_definition from INFORMATION_SCHEMA.ROUTINES to see the sp code. Is there a similar table for triggers?
Thanks,
mpv
This is how I figured out how to search all triggers in all databases for the existence of a specific string. This also shows how to only search specific databases. Im my case, I only want databases that start with SL, GR or TR.
using DB_NAME() AS ”?” in the select will put the database name in the first column of the resultset so you know which database the results came from.
sp_msforeachdb ‘
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE left(”?”,2) IN(”SL”,”GR”,”TR”)
)
BEGIN
USE ?
SELECT DB_NAME() AS ”?”,OBJECT_NAME(id),text
FROM syscomments
WHERE [text] LIKE ”%mysearchstring%” AND OBJECTPROPERTY(id, ”IsTrigger”) = 1
END
‘