Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rahul Raj
Starting Member
41 Posts |
 Posted - 2015-02-16 : 20:30:36
|
| Hi,Due to some SSQL application upgrade, some unnecessary columns have been added to the tables and the stored procedures. The values in these columns are NULL and these columns have been added at the end of the tables and some SP.As my application doesn't require these columns thereby I have re-created the old tables using other environments where these columns were not present.Can I do the same thing for stored procedures as well i.e. to drop the SP and recreate using other environment which are not having the unnecessary columns.please suggest if I need to look into some other aspects for recreating the SP.Thanks in Advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-16 : 20:33:19
|
| What do you mean by "using other environment"? You can just ALTER stored procedures to change the code.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2015-02-16 : 20:35:04
|
| You can drop and recreate; however, when you do that, any permissions that were granted on those stored procedures will be lost. So you should script the permissions and reapply them. Alternatively you can ALTER the stored procs to change the code, which will retain the permissions |
|
|
|
Rahul Raj
Starting Member
41 Posts |
Posted - 2015-02-16 : 21:17:10
|
| Thanks James and Tara for the prompt response!I will try to alter the procedure instead of dropping. Also,when I am trying to get a list of SP having the unnecessary columns using the below query; I am getting some SP which are not even having the column. Can you please suggest what might be the reason.select distinct so.namefrom syscomments scinner join sysobjects so on sc.id=so.idwhere sc.text like '%colname%' |
|
|
|
Rahul Raj
Starting Member
41 Posts |
Posted - 2015-02-16 : 21:19:07
|
| Hi Tara,By "other environments" I mean the sql server environments which are having the old definition of the SP without the additional columns which are not required in my application. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-02-17 : 01:37:19
|
quote:
You can drop and recreate; however, when you do that, any permissions that were granted on those stored procedures will be lost. So you should script the permissions and reapply them. Alternatively you can ALTER the stored procs to change the code, which will retain the permissions
JamesK - I assume you mean object level permissions? If you had a role based security - then the stored procedures wouldn't be lost on recreate.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
Dropping and re-creating Stored Procedure