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
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 ?