February 10, 2009 at 11:47 pm
Dear sir,
i want to change datatype in my storeprocedure parameter .my database 500 stored procedure.how can change parameter size in easy way.
LAv
February 11, 2009 at 5:50 am
Do you mean you have 500 stored procedures that all have the same parameter and you want to change the data type? That's a pretty odd configuration.
Best bet would be to script out all the procedures to a single text file. There are a lot of ways to do this, but a relatively simple one (although not automatable) is to highlight the store procedures folder in the Object Explorer window. Have the Object Explorer details window open. This will show a list of procedures. Highlight the list, right click, select Script Stored Procedure As and then Drop and Create to and then you can select a file or a query editor window or the clipboard or whatever. This will generate the scripts to drop and recreate your procs. Perform a search & replace and then you can run the script to rebuild the procedures.
Be careful doing this.
Other mechanisms, such as using PowerShell to script out alter statements using SMO would be much more automated and controlled, but the above should get you through.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply