sp_rename tablename and dependencies problems

[fredong]

Hi,
I am using SQL 2000 and I have renamed AAA table to BBB table and created a new table named AAA. However, the BBB table has 20 dependencies (SP and Trig) that I wanted to move back to the new AAA table. I do not want to drop and recreate the dependencies. Is there a way to move to the new AAA table. Please advise. Thanks.

 

[onpnt]

You can do this with DTS and the Copy SQL Server Objects task I believe.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Catadmin]

I know you don't want to do a drop & Create, but you can easily do one by using the Generate SQL Script task in Enterprise Manager. Generate a create, then change the table name in all the Creates in QA by navigating to Edit -> Replace. Generate the Drops and place them above the Creates.

Once that's done, run the whole thing (backup the DB before you do so, though).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sendba]

Hi All

In SQL SERVER 2000 the job history gets automatically deleted.If it goes beyond 250 Steps the old history gets deleted.
I want the job history to retain atleast 500 rows.
Is that possible in SQL Server.If so please let me know

Thanks in advance

sendba

 

[Catadmin]

Sendba,

Please post this in a new thread. Not in someone's current thread.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fredong]

you think I can do an update on the sysdepends table. You think this will work?

 

[mrdenny]

No that wont work. Sysdepends isn't authoritive. As Catadmin said you will need to script out the objects, drop and recreate them back to the correct table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com