SQL Server help...
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=10703
Printed Date: 30 March 2026 at 12:10pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL Server help...
Posted By: MrCarl
Subject: SQL Server help...
Date 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 |
Replies:
Posted By: dpyers
Date Posted: 02 June 2004 at 11:49am
|
Access or MS SQL? ------------- 
Lead me not into temptation... I know the short cut, follow me. |
Posted By: Mart
Date Posted: 02 June 2004 at 3:14pm
Topic title: Topic: SQL Server help... |
Posted By: MrCarl
Date 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 |
Posted By: Semikolon
Date 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 |
Posted By: ljamal
Date 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. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: dpyers
Date Posted: 03 June 2004 at 2:41pm
|
SFAIK, there's no one-click solution. 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. |
Mart wrote:Posted By: Bluefrog
Date 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.
------------- http://renegademinds.com/" rel="nofollow - Renegade Minds - Guitar Software http://renegademinds.com/Default.aspx?tabid=65" rel="nofollow - Slow Down Music |