Web Development

General discussion


SQL Indexes/table resetting

By c-mackenzie ·
I have a SQL Server 2000 DB that has numerous tables that I would like to reset back to how they would have been just after creation( i.e. blank fields throughout) I'd like a number 1 to appear in the first index column when a new record is added. At the moment when I add a new record number 14 appears but i cannot access any of the records prior to that to delete them. How can I wipe out all of the existing records from thast table whilst still keeping the table intact ?

Many thanks for your help

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to SQL Indexes/table resetti ...

You have wiped the extant records out, but the autonumber stays set to the next. In Access, and probably in SQL Server, you can open the table in design view, save it to a new name, delete the old table and rename the new table: that will give you numbering from 1 onwards.

Collapse -

by sjohnson175 In reply to SQL Indexes/table resetti ...

In SQL Server I believe you can reset the identity seed on an identity column in an empty table.

Collapse -

by toni.myyrylainen In reply to SQL Indexes/table resetti ...

In SQL Server you can use command TRUNCATE TABLE table_name.

In SQL BOL it says:
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

Collapse -

by c-mackenzie In reply to

Many thanks, this done the trick.


Collapse -

by Dennis.Mark In reply to SQL Indexes/table resetti ...

You need to use the truncate command to delete the data and reset the identity column back to its initial seed value. The delete command only deletes the data, but does not reset the identity column. The syntax of the truncate command is as follws:


where TableName is the name of your table.

To apply this to several tables execute several truncate statements, one for each table you wish to truncate. If you want to truncate all the tables in the database, you may want to write a stored procedure with a truncate statement that cycles through the table names stored in the sysobjects table. Hope this helps.

Collapse -

by c-mackenzie In reply to SQL Indexes/table resetti ...

This question was closed by the author

Related Discussions

Related Forums