How do you clear tables of all data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do you clear tables of all data?

Author

Topic

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-28 : 13:59:43

What is the best way to empty out a table to reset its identity field back to 1?

I was going to do "Generate SQL Script" for all the tables I wanted to reset and then drop and re-create each table. Is there a better way to empty out all of your tables?

Kristen
Test

22859 Posts

Posted - 2006-02-28 : 14:10:07

If you don't have any Foriegn Keys on the tables then:

TRUNCATE TABLE dbo.MyTable
DBCC CHECKIDENT ('dbo.MyTable', RESEED)

Kristen

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-28 : 14:17:28

I have a foreign key on most of my tables :(

What would happen if I did that to a table with a foreign key?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-28 : 14:29:14

quote:
Originally posted by Billkamm

I have a foreign key on most of my tables :(

What would happen if I did that to a table with a foreign key?

You can't truncate a table that is referenced by a foreign key. You would have to use delete to remove the data. Of course, you would have to delete the rows in the other table that reference it before you could delete the data.

You can truncate a table that only has a foreign key that references another table.

CODO ERGO SUM

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-28 : 14:37:35

I'm going to assume if A references B which references C as long as I truncate in the order of C, B, and then A I should be fine. Anyway thanks for the help. I'm going to try these out on my local test copy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 14:41:27

quote:

I'm going to assume if A references B which references C as long as I truncate in the order of C, B, and then A I should be fine. Anyway thanks for the help. I'm going to try these out on my local test copy.

It still will not let you do truncate.

Why does it matter what minimum value you have in the tables? The value should have no meaning.

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-03-01 : 02:03:26

"I have a foreign key on most of my tables"

I reckon your best bet would be to create a script to DROP FK for all tables, and a separate script for CREATE FK.

Then create a script to TRUNCATE TABLE for all tables, and reset Identity for those tables to which it applies.

I'm sure its possible to find out which tables have identity, but I'm in a bit of a rush just now, but you should be able to do something like:


SELECT	'TRUNCATE TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME
FROM	INFORMATION_SCHEMA.TABLES
WHERE	TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

SELECT	'DBCC CHECKIDENT (''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', RESEED)'
FROM	INFORMATION_SCHEMA.TABLES
WHERE	TABLE_TYPE = 'BASE TABLE'
	... AND table has Identity column ...
ORDER BY TABLE_SCHEMA, TABLE_NAME

Then:

Run the DROP FK script
Run TRUNCATE TABLE and Reset Identity script
Run CREATE FK script

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-01 : 02:36:04

for all tables in database,
your first approach will be ok

saves a lot of effort

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-03-01 : 05:25:10

"saves a lot of effort"

But you have to generate a script each time you want to do it if anything has changed ...

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-01 : 08:31:26

which means, you need to do some planning and designing to avoid re-doing it

--------------------
keeping it simple...

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-01 : 08:39:51

Thanks for the help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-01 : 12:17:03

well i don't know... but wouldn't it be easier to just
script the entire db, drop the db and run the script again to build the db??

Go with the flow & have fun! Else fight the flow

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-01 : 12:33:57

spirit1: I was going to try that, but SQL Server 2000 didn't output all the tables when I right-clicked the database and went to Generate SQL Script, so I figure if I was going to have have to create a script for each table I might as well look for another way to do it.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-01 : 12:37:55

No, just make sure you click the show all button and make sure you have the script database, indexes etc ticked on the options tab..

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-01 : 13:08:34

RickD: All the objects I wanted to script were grayed out. Not sure why.

EDIT: I see the "Show All" button now. I think that is slightly confusing to phrase it as "Show All", because the items ARE already showing, they are just disabled.

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-01 : 13:59:46

The database script approach didn't work too well. I lost all of my diagrams and users and some other things.

Oh well I restored the backup and I'm going to try the other method

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-02 : 01:38:43

did you try to script "everything" out? schemas, contraints, users,permissions,objects?

--------------------
keeping it simple...

lino_sql
Starting Member

3 Posts

Posted - 2006-03-02 : 03:44:17

What's happenning!

A Chinese Boy 24 ages

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 05:34:59

"did you try to script "everything" out? schemas, contraints, users,permissions,objects?"

Diagrams? User logins may be a bit marginal too - it will probably drop then first and recreate them, which will take away any permissions to other databases.

I'm with the original Truncate plan, myself!

Kristen

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-03-02 : 10:53:15

Ok Kristen let me make sure I have all my logic straight before I take a stab at this:

- Generate Script For all Foreign Keys
- Drop all Foreign Keys
- Trunacte and Reseed any tables I wish to reset
- Create all Foreign Keys with generated script

Also, to generate the script to create all of the keys I right-clicked on the database and generated script with only "All Tables" and "Script PRIMARY Keys, FOREIGN keys, defaults, and check constraints" checked. This appeared to work well. However, I'm not sure how I could generate a script to drop all of those.

In addition would it be a bad to write dynamic SQL to loop through all the table names to execute the TRUNCATE and RESEED functions? Would this cause any problems?

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 11:38:17

"I'm not sure how I could generate a script to drop all of those"

Select the tables.

On the "Formatting" tab select the DROP object (deselect anything else)

On the Options tab select PK + FK

In the generated script all the DROP will be at the top, followed by all the CREATE.

All the FK DROP, and the FK create, will be together in a block - so you will just need to delete all the other blocks that relate to PKs and any other check constraints, and then split the script into two - the DROP commands and the CREATE commands.

"In addition would it be a bad to write dynamic SQL to loop through all the table names to execute the TRUNCATE and RESEED functions"

Sounds alright to me.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources