December 11, 2012 at 12:05 am
Hi Team,
is it possible to get all stored procedures script using a cursor.
Can u please provide the query
December 11, 2012 at 12:16 am
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
December 11, 2012 at 2:10 am
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
December 11, 2012 at 3:20 am
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'
December 11, 2012 at 5:34 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply