Post reply

Remove all data from a database

  • August 13, 2011 at 1:37 pm

    #390189

    Comments posted to this topic are about the item Remove all data from a database

    The Fastest Methods aren't always the Quickest Methods

  • August 15, 2011 at 6:31 am

    #1368562

    I might be off on a tangent, but does this handle SEEDs?

  • August 15, 2011 at 9:49 am

    #1368695

    It doesn't handle SEEDs as it's not doing a truncate, from what I know a truncate will only reset the SEEDs on a table. If you try TRUNCATE you'll get an error like Cannot truncate table 'dbo.MyTable' because it is being referenced by a FOREIGN KEY constraint.

    I will make updates to the script to drop foreign keys and reassign them again and then you'll be able to truncate and therefore reset SEEDs 😀

    The Fastest Methods aren't always the Quickest Methods

  • August 15, 2011 at 9:54 am

    #1368697

    Ack. I did wonder as you could end up with, say, a CustomerID starting part way along in a supposed virgin database.

  • August 15, 2011 at 10:15 am

    #1368707

    Umm, couldn't you replicate with something like this?

    USE databasename

    GO

    DECLARE @srows INT, @erows INT

    SELECT @srows = SUM(a.rows) FROM (SELECT object_name(object_id) AS TableName, rows FROM sys.partitions WHERE index_id IN (0,1)) a

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

    EXEC sp_MSForEachTable 'DELETE FROM ?'

    EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

    EXEC sp_MSForEachTable 'DBCC CHECKIDENT ( "?", RESEED, 0)'

    SELECT @erows = SUM(a.rows) FROM (SELECT object_name(object_id) AS TableName, rows FROM sys.partitions WHERE index_id IN (0,1)) a

    PRINT 'Start: Total rows of data: ' + CONVERT(VARCHAR(100),ISNULL(@srows,0))

    PRINT 'End: Total rows of data: ' + CONVERT(VARCHAR(100),ISNULL(@erows,0))

    --edit--

    I guess not, well, not quite. But it should clear all of the data (I don't have a test database that I'm willing to run it against to check at the moment)

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 17, 2011 at 6:01 am

    #1369527

    I have added

    EXEC sp_MSForEachTable 'DBCC CHECKIDENT ( "?", RESEED, 0)'

    to the end of my script. This resets the seeds where possible. Will just need to wait for it to be approved before it'll show up in the script on SqlServerCentral

    Thanks for that bit of code 🙂

    The Fastest Methods aren't always the Quickest Methods

  • May 12, 2016 at 7:17 am

    #1878586

    Thanks for the script and updates.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply