Stored procedure to copy tables & also preserve indexing/schema

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello,

I created a stored procedure that renames a table to OLD_xxxxx and replaces
that table with another (copy) that resides on a different database. I pull
the tablename names through the use of a cursor table and construct a SELECT
INTO statement as follows

'SELECT * INTO DB1.dbo.' + @tableName + ' FROM DB2.dbo.' + @tableName

It works great especially since there are 80+ tables (of the 900+ user tables
that presently exist) that need to be copied from one database to another.
The drawback is that it doesn't preserve the indexing/foriegn key
constraints. Is there a way to do this without having to deal with DTS or
creating additional scripts?? Ideally I would like to replace the "SELECT *
INTO" statement with something that not only does a copy but also preserves
the indexing! Does such a command exist???? I was hoping there would be an
alternate way to do a full blown copy that includes creation of the INDEXES,
KEYS etc. For example there is a stored procedure called SP_RENAME. I was
hoping there was an SP_FULLCOPY or something that might get me what I was
looking for?

If not perhaps there is a way I can automatically pull (from my stored
procedure) the ALTER commands to create the keys etc and execute them on the
fly?
That would do it. I have looked into SQL-DMO and automatically generating
scripts through Enterprise Manager but that doesn't get me what I need
from a stored procedure standpoint.

Any helpful information you might be able to supply would be GREATLY
appreciated!
.

Relevant Pages

  • Re: Extended Stored Procedure: Get the current db of the client
    ... with the caveat that you don't recommend it because Microsoft ... of a stored procedure versus umpteen of the same stored procedure spread ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ...
    (microsoft.public.sqlserver.odbc)
  • Trying to set up a SQL Server Agent Account and I hit a wall....
    ... Our database is on SQL Server 2005, however it is set to compatibility mode ... You may need to set the compatibility level ... of the current database to a higher value to enable this feature. ... for the stored procedure sp_dbcmptlevel. ...
    (microsoft.public.sqlserver.security)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ... Did you ever write an extended stored procedure? ... different than an extended stored procedure, so that is not giving you want ...
    (microsoft.public.sqlserver.odbc)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... Thanks for the link Sue. ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ... Did you ever write an extended stored procedure? ...
    (microsoft.public.sqlserver.odbc)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)