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

Peter,
the option to take FKs and indexes is part of any type of replication. As a
replacement of what you are trying to achieve, I suggest snapshot
replication. The FKs will be created if both the PK and the FK tables exist
in the same publication. If not, you'll have to use pre-snapshot scripts,
which are hand-crafted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)