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 (http://blog.SQLAuthority.com) , BOL
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
Hello Shashi,
You are absolutely correct. I have example of using system command is here : SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Pinal Dave, you are nothing but just an over smart guy
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.
@stalin,
I am not sure.
@mohamed,
http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/
Regards,
Pinal Dave (http://www.SQLAuthority.com/)
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’
@Ashutosh
You can download all of these and stick it to your desk, so that you need not remember anything, just see and start using these shortcuts….
SQL Server 2000 – Query Analyzer Short Cuts
———————————————————–
http://blog.sqlauthority.com/2007/01/20/sql-server-query-analyzer-shortcuts/
http://blog.sqlauthority.com/2008/04/27/sql-server-query-analyzer-shortcuts-part-2/
SQL Server 2005 – SQL Server Management Studio Short Cuts
——————————————————————————–
http://blog.sqlauthority.com/2007/05/04/sql-server-download-sql-server-management-studio-keyboard-shortcuts-ssms-shortcuts/
SQL Server Cheat Sheet (Very good one page information)
—————————————————————————
http://blog.sqlauthority.com/2009/05/20/sql-server-download-pdf-sql-server-cheat-sheet/
Enjoy.
~ IM.
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
‘
I forgot to note, the double ticks ” are not double quotes “. They are two single ticks ‘ next to eachother since the command passed to sp_msforeachdb is already single quoted.
Hi
i have upgraded sql 2008 from 2005, after upgraded i got many problem in reports, the alignments are not good and also page setup not correct, please explain me why it is happening, and how to resolved this problem.
thanks
selva
Hello Masters…How i update table1 when i delete data from table2 through Stored procedure.
Sir,
In SQL 2000 ,how can we get the text of all Stored Procedure together ? In SQL 2005, I was able to get it from this code ,but there are some structures which are not present in SQL 2000 like (select * from sys.sql_modules m )
The code for SQL 2005
ALTER PROCEDURE [dbo].[usp_SearchStoredProcedure_Generate_ViewSPText]
@SearchTerm VARCHAR(1000)
AS
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘ EXEC sp_helptext ”’ + s.name+’.'+OBJECT_NAME(m.object_id)+””
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE Definition LIKE ‘%’+@SearchTerm+’%’ AND
OBJECTPROPERTY(m.object_id, ‘IsProcedure’) = 1;
EXEC sp_executesql @SQL
——————————————–
This is for calling them in single column
– exec storedprocedureexecution
ALTER procedure [dbo].[storedprocedureexecution]
as
begin
create table #temp
(
SPName varchar(max)
)
–insert into #temp exec dbo.usp_SearchStoredProcedure_GenerateHelpText ”
insert into #temp exec dbo.usp_SearchStoredProcedure_Generate_ViewSPText ”
select * from #temp
end
hi, i want to display data in form.i have used labels and textboxes in forms and want to retreive data from my database to get data in the textboxes according to userid.i m able to reteive userid in page but now i m not gettin g how to get values in my textboxes from database. im using ASP.net 3.5 with C# and Sql server 2005.
kindly help me.
your description is very good always
Excellent site ;its really very help ful
ecellent
I have written a storeprocedure in a well formatted manner, and after saving I try to open the same procedure using the “sp_helptext “. Which actually opens the procedure but formatting is destroyed.
So…What Is Solution For That
You need to set the result mode to text (press ctrl+T) and execute sp_helptext spname
I Have One Table [BIODATA] In Which One Field Like [Emp_Photo] With image Datatype And I Store Employee Image As Binary In This Table
In This Table Have About 2000 Employee Record With Image
When I Write [Select * From Biodata] It Will Take Large Amount Of Time For Complete Execution
I Write [Select Emp_Code,Emp_Name..... From Biodata] Except [Emp_Photo] Field
In Few Second It Will Execut
So.. Any Solution For Query With [Image] field Can Be Faster
You cannot display images in the Query analyser. So omit that column when querying. Also you need to use front end application to display the images
Hello Sir,
I need definition of stored proc
by arun
exec sp_helptext ‘procedure_name’
ALTER VIEW [dbo].[vwProviderUtilization]
AS
SELECT
dbo.UsageHourseMinutes(O.AllocationStartDate, ISNULL(O.ActualReleaseDate,GETDATE())) AS Utilized_Time
this my view, if ActualReleasedate is null then am taking the getdate, if user select the date before the Getdate the utlizied time will be calculated on the getdate it rong how to solve the problem not in storeprocedure i need the result in veiw..
What is the actual problem with the view?
sp_helptext is not showing the defination please revert thanks in advance
i want the name of procedure which is going to be deleted in a table how this can achieve
thanks
Thanks. Exactly what is was looking for.
Thanks,,,it working
thanks very useful information
How to view the stored procedure definition other than query in Query analyzer.Do you other other options .Example right click on store procedure goto ….etc like
Why do you want not to use a query analyser for this. It is easy to view definition of a stored procedure using query analyser
How to view, all users’ name of a database, in ms sql server 7 ?
Hi Pinal
I have restored SQL Server 2008 Database to Other New DB Server and In the new server – I want to create around 800 Orphaned windows users, Please let me know any short cut method to created Windows Login from the Users list of Restored database\Security\Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole 1 and isapprole 1
and (u.name ‘INFORMATION_SCHEMA’ and u.name ‘guest’
and u.name ‘system_function_schema’)
how can i open procedure with perfect alignment in sql server using a command like sp_helptext
Set the result mode of the query window to text (Press CTRL+T) and run sp_helptext
Please tell me the query to view the definition of a table
Hello Pinal Sir!
How to check stored procedures with same scripts or output results?
This is not clear. Can you give us more information?
In Denali 2012 RTM Version, SP_HELPTEXT is creating extra newlines while generating DB Objects code which is very problematic. The difference I found in SP_Helptext of denali and older Version was as below –
Old Versions –
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
Denali Version –
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate Catalog_default).
Even we are not allowed to modify SP_HELPTEXT Sproc. Please help or suggest.
Greetings: I am debugging some MS SQL 2K8r2 stored procedures and wanted to generate the line number of where I am catching various lines of code. Can you point me in the right direction?
Thanks!
BTW: Love your site!
hello sir………
can you pleas tell me how image and text data can retrieve together from sql database….to a single page……
thanks x 1000000!!
by using this code u wl get code of trigger
create procedure sp_view_trigger(@trig_name varchar(max),@trig_view text)
as
begin
select c.text ‘code of trigger’
from sysobjects t inner join syscomments c
on t.id=c.id
where t.name=@trig_name
end
declare @viewdata varchar(max)
execute sp_view_trigger ‘name_of_trigger’ ,@viewdata
print @viewdata