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
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
can anyone explain it to me pls
USE [bakundpur2013-14]
GO
/****** Object: Index [aaaaaStockAdjust_PK] Script Date: 07/24/2013 18:33:17 ******/
ALTER TABLE [dbo].[StockAdjust] ADD CONSTRAINT [aaaaaStockAdjust_PK] PRIMARY KEY NONCLUSTERED
(
[Shop_Code] ASC,
[Bottle_Code] ASC,
[StockDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
–Show Stored Procedures
select p.[type]
,p.[name]
,c.[definition]
from sys.objects p
join sys.sql_modules c
on p.object_id = c.object_id
where p.[type] = ‘P’
–and c.[definition] like ‘%foo%’
ORDER BY p.[name]
_______________
SELECT OBJECT_NAME(object_id) ProcedureName,
definition
FROM sys.sql_modules
WHERE objectproperty(object_id,’IsProcedure’) = 1
ORDER BY OBJECT_NAME(object_id)
Pinal you are amazing.. i found every thing in your blog
Gaurav – I am glad that you are liking it,
This is what I wrote to get all procedures in separate SQL files in order to commit them in SVN and to follow changes made on them.
But (yes), there is a misery about this: sometimes, for no identified reason, instead of returning ‘CREATE PROCEDURE [dbo].[procedure_name]’, sp_helptext is only returning ‘CREATE PROCEDURE procedure_name’
When I use ‘script stored procedure as create to’, the script is containing ‘CREATE PROCEDURE [dbo].[procedure_name]’.
This is the only problem I have to completely finish this.
If someone is interested in:
The stored procedure is called by a client that is creating one file for each stored procedure using ‘START_PRC’ and ‘END_PRC’ to find the starting and ending points of a procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— CREATED TO WRITE THE CODE IN SEPARATE SQL FILES
CREATE procedure [dbo].[XXX_StoredProceduresInText]
@ONLY_NAMES BIT
AS
set nocount on
DECLARE @TEXT AS NVARCHAR(MAX)
DECLARE @SCRIPT AS NVARCHAR(MAX)
DECLARE @Cursor Cursor
DECLARE @CursorTB Cursor
DECLARE @PROC NVARCHAR(MAX)
–N’sp_helptext ”[dbo].[‘ + DbObjects.Name + ‘]”;’
Set @Cursor = CURSOR FOR
SELECT
m.uses_ansi_nulls, m.uses_database_collation, m.uses_quoted_identifier,
N’sp_helptext ”’ + DbObjects.Name + ”’;’
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
INNER JOIN sys.sql_modules m ON m.object_id = DbObjects.object_id
WHERE SysSchemas.Name = ‘dbo’ and DbObjects.name not like ‘sp_%’
and DbObjects.name not like ‘X%’ and
DbObjects.name not like ‘dt_%’
AND (DbObjects.Type in (‘P’, ‘FN’))
order by DbObjects.Type DESC, DbObjects.Name
DECLARE @TEMP TABLE (numero int IDENTITY(1,1), Xansi_nulls bit, Xdatabase_collation bit, Xquoted_identifier bit, OBJECT_NAME VARCHAR(500), SCRIPT NVARCHAR(MAX))
DECLARE @SCRIPT_TB TABLE (numero int IDENTITY(1,1), SCRIPT NVARCHAR(MAX))
DECLARE @ansi_nulls bit, @database_collation bit, @quoted_identifier bit
OPEN @Cursor
FETCH NEXT FROM
@Cursor
INTO
@ansi_nulls, @database_collation, @quoted_identifier, @TEXT
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @SCRIPT_TB
INSERT INTO @SCRIPT_TB EXEC(@TEXT)
IF(CHARINDEX(‘–END_PRC–‘, @TEXT) = 0)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘–END_PRC–‘)
Set @CursorTB = CURSOR FOR
SELECT SCRIPT FROM @SCRIPT_TB
OPEN @CursorTB
FETCH NEXT FROM
@CursorTB
INTO
@PROC
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
@TEMP
(Xansi_nulls, Xdatabase_collation, Xquoted_identifier, SCRIPT)
VALUES( @ansi_nulls, @database_collation, @quoted_identifier, @PROC)
FETCH NEXT FROM
@CursorTB
INTO
@PROC
END
DEALLOCATE @CursorTB
FETCH NEXT FROM @Cursor INTO
@ansi_nulls, @database_collation, @quoted_identifier, @TEXT
END
DELETE FROM @SCRIPT_TB
DEALLOCATE @CURSOR
–+ CHAR(13) + CHAR(10)
IF(@ONLY_NAMES = 0)
Set @Cursor = CURSOR FOR
SELECT T.Xansi_nulls, T.Xdatabase_collation, T.Xquoted_identifier, T.SCRIPT
FROM @TEMP T ORDER BY numero
ELSE
Set @Cursor = CURSOR FOR
SELECT T.Xansi_nulls, T.Xdatabase_collation, T.Xquoted_identifier, T.SCRIPT
FROM @TEMP T
WHERE
CHARINDEX(‘CREATE ‘, T.SCRIPT, 0) > 0 AND
(
CHARINDEX(‘ PROC ‘, T.SCRIPT, 0) > 0 OR
CHARINDEX(‘ PROCEDURE ‘, T.SCRIPT, 0) > 0 OR
CHARINDEX(‘ FUNCTION ‘, T.SCRIPT, 0) > 0
)
ORDER BY numero
DECLARE @ANSI VARCHAR(100)
DECLARE @DB_COLLATION VARCHAR(100)
DECLARE @QUOTED VARCHAR(100)
OPEN @Cursor
FETCH NEXT FROM
@Cursor
INTO
@ansi_nulls, @database_collation, @quoted_identifier, @TEXT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ANSI = ”
SET @QUOTED = ”
SET @DB_COLLATION = ”
IF
(
@ONLY_NAMES = 0 AND
(CHARINDEX(‘CREATE ‘, @TEXT, 0) > 0 AND
(
CHARINDEX(‘ PROC ‘, @TEXT, 0) > 0 OR
CHARINDEX(‘ PROCEDURE ‘, @TEXT, 0) > 0 OR
CHARINDEX(‘ FUNCTION ‘, @TEXT, 0) > 0
))
)
BEGIN
–THIS IS A NEW PROCEDURE
IF(@ansi_nulls > 0)
BEGIN
SET @ANSI = ‘SET ANSI_NULLS ON’
END
ELSE
BEGIN
SET @ANSI = ‘SET ANSI_NULLS OFF’
END
IF(@quoted_identifier > 0)
BEGIN
SET @QUOTED = ‘SET QUOTED_IDENTIFIER ON’
END
ELSE
BEGIN
SET @QUOTED = ‘SET QUOTED_IDENTIFIER OFF’
END
IF(CHARINDEX(‘–START_PRC–‘, @TEXT) = 0)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘–START_PRC–‘)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@ANSI)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘GO’)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@QUOTED)
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(‘GO’)
END — OF NEW FUNCTION OR PROCEDURE
INSERT INTO @SCRIPT_TB (SCRIPT) VALUES(@TEXT)
FETCH NEXT FROM
@Cursor
INTO
@ansi_nulls, @database_collation, @quoted_identifier, @TEXT
END
DEALLOCATE @Cursor
select S.SCRIPT from @SCRIPT_TB S
order by S.numero
Thanks a lot, for being consistent and very helpful instead of fill out all the page with scumware
Hello Sir i have one *.mdf file database but dont have his LDF File so it can not attach in my laptop.
is that any way to attach or see his tables and store procedure without SQl Server.
Thanks
Montu
Hello sir,
I have 10 databases in a particular SQL server. I am using stored procedure. I want to get the content of a particular stored procedure, don’t want to search one by one. is there a query which would search in all the database and show me the results ??
can we change table name and it will automatically change in stored procedures and views both
????????????????????/