copy table data and indexes into another table

I was thinking of putting the data and then altering the table, but your solutions seem fine too.. but my question was "how" ?

bu thanks for the idea..

Open enterprise manager, right click on your database, choose "All Tasks", Generate SQL Scripts.

You can take the scripts and run them from VB using the connection object with Execute.

Brett

>> but my question was "how" ?<<
How what?  Generate the script or the Insert?

The easiest way to generate the script for the structure is to use Enterprise Manager, highlight all the appropriate tables, right click "All Tasks" | "Generate SQL Scripts..."

As far as doing the INSERT that is dependent on the structure of your tables, but something like this:
INSERT db2.dbo.test
Select *
FROM db1.dbo.test

Although it is best to explcitly name the columns.

I am writing a backup module for a program that has 150-200 tables, each have indexes, foreign keys. the tables are like xxx_10_13 , xxx_14_15. each table names means another firm, so  the user should choose the ones to backup, and the script would get the name of the table from dropdown and copy it to the backup database.

so the script (i suppose it would be stored procedure) should get the constants as well as data.

- create table
- insert the data
- alter the indexes and foreign keys.

 is there any sp that copies indexes, foreign keys, defaults in mssql  ? or should i do it looping through sysobjects ?

sounds like a nightmare.  With something so small, why don't you just use the existing SQL Backup?  Are you also going to allow them to restore in this fashion???

yeah, it is a nightmare.. but they don't want to take full backup. cause they would use this program both for backup and giving data to other program users. and the given data sould not be the full backup but only some tables (which is a security issue i couldn't even care to understand).

but i am in a very bad situation now, and need help from experts. that's why i post this question.

I agree.  Not something I personally would undertake. Unfortunately we cannot give you detailed instructions how to complete this, just a broad outline.

i need only hints about using sysobjects table to loop through table related items to generate them automaticly. like getting the indexes of a table and create the same indexes in another one through a stored procedure.. if i could do that, i believe i can write a full functional sp that does that job.

First hint--don't ever user the sysobjects tables.  use information_schema.tables, information_schema.column_names, stored procedures (sp_fkeys, sp_pkeys, etc) to get the information.  Querying the system tables is usually a bad idea.

i tried to use sysobjects  because i saw a script like :

select sysobjects.name Table_Name,
(Select rows from sysindexes
 where id = sysobjects.id
 and indid = 0) Rows
from
 sysobjects
where
 xtype = 'u'
 and objectproperty(sysobjects.id, 'TableHasIndex') <> 0

which gives the tables without indexes...

but let me look at SQLDMO a bit.

if it helps anyone, i rewrote

http://www.databasejournal.com/img/GenDependScript.vbs_

this script written and it worked well.

thanks for the sql-dmo hint

and another method i found is

http://www.databasejournal.com/features/mssql/article.php/2205291