SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Bulk replace input parameter in SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 03/26/2014 :  17:09:52  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi All,

I made a mistake and made a ton of SP with the wrong parameter type

@site as nvarchar(60)

and should be

@site as varchar(50)

IS there a Query i can write to do this?
Are there any tools to do this kind of stuff?

Ahhh also got a list op SP i want to Add 'WITH RECOMPILE'

Thanks a lot

Edited by - mike13 on 03/26/2014 17:26:13

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 03/28/2014 :  09:20:38  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Found this that solved my problem:

SELECT
b.Name AS [ObjectName],
CASE WHEN b.type ='p' THEN 'Stored Procedure'
WHEN b.type ='v' THEN 'View'
ELSE b.TYPE
END AS [ObjectType]
,a.definition AS [Definition]
,Replace ((REPLACE(definition,'OLD Value','New Value')),'Create','ALTER') AS [ModifiedDefinition]
FROM sys.sql_modules a
JOIN
( select type, name,object_id
from sys.objects
where type in (
'p' -- procedures
,'v'--views
)
and is_ms_shipped = 0
)b
ON a.object_id=b.object_id
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000