April 15, 2008 at 2:55 am
Hi,
Does anyone know if it is possible to extract a stored procedure script from say syscomments? I want to script up a stored proc from a database, and then run into a second db, programatically.
Many thanks
April 15, 2008 at 4:55 am
you mean all procedures in the database
if single then try this
sp_helptext 'procedure name'
April 15, 2008 at 5:05 am
[font="Arial"]Sorry if I'm being a numpty, but do you mean something like this (although, not exactly this, as it doesn't work!):
DECLARE @sql VARCHAR(4000)
SET @sql = sp_helptext 'AddRecord'
EXECUTE Scratch.dbo.sp_executesql @sql
How do I get the returned sp into my variable?
[/font]
April 15, 2008 at 5:23 am
Rummaging about in the other forums, I found this solution, posted by GilaMonster (thanks Gail).
It works perfectly
USE Scratch
GO
Select name, definition
FROM Test.sys.sql_modules sm inner join Test.sys.objects o on sm.object_id = o.object_id
where name = 'AddRecord'
IT works perfectly here:
DECLARE @sql NVARCHAR(4000)
SELECT @sql = definition
FROM Test.sys.sql_modules sm inner join Test.sys.objects o on sm.object_id = o.object_id
where name = 'AddRecord'
Execute Scratch.dbo.sp_executesql @sql
April 15, 2008 at 5:32 am
Pleasure.
Just declare your variable as nvarchar(max), otherwise you risk truncation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply