Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursor to generate Stored procedures Script Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 12:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 227, Visits: 722
Hi Team,

is it possible to get all stored procedures script using a cursor.

Can u please provide the query
Post #1394910
Posted Tuesday, December 11, 2012 12:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:06 AM
Points: 312, Visits: 164
I am using temp table instead of cursor.

declare @spname varchar(100), @sptext varchar(max), @cnt int, @i int
select @spname = '', @sptext = '', @cnt = 0, @i = 1;

create table #spnames(id int identity(1,1), spname varchar(100))

insert into #spnames(spname)
select distinct schema_name(schema_id) + '.' + name from sys.objects where type = 'P'

select @cnt = count(1) from #spnames

while (@i <= @cnt)
begin
select @spname = spname from #spnames where id = @id
select @sptext = object_definition(object_id) from sys.objects where = schema_name(schema_id) + '.' + name = @spname
print @sptext
set @i = @i+i
end
GO
drop table #spnames
Post #1394914
Posted Tuesday, December 11, 2012 2:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 66, Visits: 437
You might also try this with a TVF.

CREATE FUNCTION dbo.FnGetDefinition(@ObjId INT)
RETURNS @DefTable TABLE(ObjectDef VARCHAR(MAX))
AS
BEGIN
INSERT INTO @DefTable SELECT object_definition(@ObjID)
RETURN
END;
GO
SELECT P.name,D.ObjectDef FROM sys.procedures As P
CROSS APPLY dbo.FnGetDefinition(p.object_id) As D

Post #1394947
Posted Tuesday, December 11, 2012 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
Or just query the sys.sql_modules table

select 
o.name,
sm.definition
from
sys.objects o
inner join
sys.sql_modules sm
on
o.object_id = sm.object_id
where
o.type = 'p'





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1394993
Posted Tuesday, December 11, 2012 5:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 12,897, Visits: 32,105
I would basically do it the same as anthony; they are already in a table, so not temp table or cursor required.

I kind of like some of the built in functions, so I'd use those for schema/object name:
select 
OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
OBJECT_NAME(object_id) AS ObjectName,
definition AS ObjectDefinition
FROM sys.sql_modules



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395067
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse