Need to change 1000 SP's drop & re-recreate them with permissions?

Question

Need to change 1000 SP's drop & re-recreate them with permissions?

Sql Student-446896

Default port

Points: 1425
More actions
October 9, 2008 at 8:46 am

#194471

I have around 1000 procedures in a database that needs to refer a column in another database. Originally that column was in the same database as the SPs, but for some reasons it has to be deleted and moved to another database.
So i need to update all SPs that refer that table with a DATABASE_NAME..
I need to drop and re-create and grant permissions to some users.
I wrote a script with cursors that will fetch me all the SP's text (sp_helptext). I have saved all the list in a notepad and manually changing the database name and also including drop and grant scripts in the procedure.
Is there an easy way to do?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Rather than updating 1000 SP's, couldn't you just create a view named what the table used to be that references the new database / table with a SELECT * ?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

Garadin has the right idea. Simplifies the solution and, if you movie it again, you only change the view.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Garadin (10/9/2008)
Rather than updating 1000 SP's, couldn't you just create a view named what the table used to be that references the new database / table with a SELECT * ?

Been there, done that, big time saver.

Another option is to script out all the stored procedures and make the changes with the help of search and replace in text editor.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sql Student-446896 Default port Points: 1425 More actions · Answer 4

I am not familiar with views. However, will give it a try using help topics.

How do i script all the 1000 procs without using the console? I mean sp_help juist brings out the text part and not the drop re-create and permissions in the script.

Could you let me know if there is a system SP that gets all the scripts with permissions?

Steve Jones - SSC Editor SSC Guru Points: 734639 More actions · Answer 5

Use EM, right click the database and select Generate Scripts.

johncyriac SSCommitted Points: 1542 More actions · Answer 6

let me clarify the earlier solution

let your table be tbl_x which is in now in another data base ( if exists in current DB drop it)

now you create a new view

like

create view tbl_x

as

select * from newdatabase.schema.tbl_x

go

grant select on tbl_x to user1,user2,user3 .....

see here tbl_x no more a table ,its a view (means a virtual table ) as good as table

pl do post your quries in newbies section to get more simple answers ,

You should learn SQL tools like Enterprise Manager to genearate scripts of SQL objects

regards

john