| Author |
 Topic Search  Topic Options
|
MrCarl 
Newbie
Joined: 29 March 2003
Status: Offline
Points: 29
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL Server help...
Posted: 02 June 2004 at 8:45am |
Hi,
I have been setting up a database with loads of tables in the beta stages of building a system and now basically what I want is to clean out all the tables and empty them so I can start the production system. But I want all the auto numbers restting as well, I have been trying to find a way to be able to just export the structure of the tables into a new database file but I cant seem to find a way to do it??
Can anyone help?
- Carl
|
 |
dpyers
Senior Member
Joined: 12 May 2003
Status: Offline
Points: 3937
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 June 2004 at 11:49am |
|
Access or MS SQL?
|
Lead me not into temptation... I know the short cut, follow me.
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 June 2004 at 3:14pm |
Topic title:
 Topic: SQL Server help...
|
|
MrCarl
Newbie
Joined: 29 March 2003
Status: Offline
Points: 29
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 June 2004 at 9:55am |
Sorry for putting this in the wrong topic... Oops!!
Its MS SQL Server 2000 its on... Access used to be easy, you could copy and paste the tables as structure only!!
- Carl S
|
|
Semikolon
Senior Member
Joined: 09 September 2003
Location: Norway
Status: Offline
Points: 1718
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 June 2004 at 12:16pm |
|
I know it's possible to do a compat and repair in Access and all tables
will be reset if they are empty, but not sure if this is possible with
SQL Server.. have tried to find a way myself, but didn't succeed
|
|
ljamal
Mod Builder Group
Joined: 16 April 2003
Status: Offline
Points: 888
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 June 2004 at 2:38pm |
|
With SQL you can copy the database structure to a new database and then reset the identity to 0 or you can export the database to script which should set the identities appropriately.
|
|
|
|
dpyers
Senior Member
Joined: 12 May 2003
Status: Offline
Points: 3937
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 June 2004 at 2:41pm |
 Mart wrote:
Topic title:
Topic: SQL Server help...
|
- lol 
SFAIK, there's no one-click solution.
IDENTITY fields are not usually seen by a user so their actual contents should not matter. There's some good arguments around that they should never be used as each row should contain enough info to create a unique key. If not, then the data is not normalized properly.
But, to answer your question, I've used DTS to copy columns (except the IDENTITY) to a new table, add an IDENTITY field, and then drop the old table and rename the new to the old. Used to do something similar with BCP, and I believe MS SQL200 BooksOnline has a TSQL example of this. This approach however, can play havoc with foreign keys.
|
Lead me not into temptation... I know the short cut, follow me.
|
|
Bluefrog
Senior Member
Joined: 23 October 2002
Location: Korea, South
Status: Offline
Points: 1701
|
Post Options
Thanks(0)
Quote Reply
Posted: 07 June 2004 at 6:17pm |
It may just be easier to export the database as a SQL script and then run it to create all the tables that you need. I've done that in the past, and it's worked fine. Make sure that you are careful to include views and stored procedures as well as any roles, etc.
|
|
|
|
