Copy Database Structure

[OceanDesigner]

Is there a way to delete all of the values from all of the tables in a database and then initialize the counters in all of the tables so that they begin from 1 again? This is pretty easy in Access, but I cannot figure it out with SQL Server. I realize that I can detach a database to copy it, but I want to also get rid of all of the data.

Thanks - Jeff

 

[Jamfool]

--This will truncate all the data (tested on sql2000)

exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''[dbo].[dtproperties]'') Truncate Table ?'
go

--**************************************
-- Name: Reset All IDENTITY Columns
-- Description:If you have just cleaned down a database and want to reset (RESEED) the identity columns in all tables then this query will list the tables with an identity column in the current database and format the DBCC command. Avoids editing system tables direct.
-- By: Toby Riley
--
--
-- Inputs:None
--
-- Returns:None
--
--Assumes:None
--
--Side Effects:None
--This code is copyrighted and has limited warranties.
--Please see

http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.770/lngWId.5/qx/vb/scripts/ShowCode.htm

--for details.
--**************************************

SELECT 'DBCC CHECKIDENT (' + sysobjects.name + ', RESEED, 0)' AS CheckIDENT
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
WHERE (syscolumns.colstat & 1 <> 0) AND (sysobjects.xtype = 'U') AND (sysobjects.name <> N'dtproperties')