October 9, 2008 at 8:46 am
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?
October 9, 2008 at 9:30 am
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 * ?
October 9, 2008 at 9:35 am
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
October 9, 2008 at 9:44 am
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.
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]
October 9, 2008 at 10:06 am
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?
October 9, 2008 at 12:09 pm
Use EM, right click the database and select Generate Scripts.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 9, 2008 at 10:21 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply