clearing all the data in the database
-
This may seem like a rookie question, but I need to clear a backend database of all sample data and defaults for a clean install, and haven't fingured out how to do all the tables at once? I'm having to clear each table one at a time.
-
Write a script to pull the table names out of sysobjects where xtype = 'U'. Something to the effect of :
Select 'Truncate Table ' + name
From sysobjects
Where xtype = 'U'
Take the result set and then execute it in another window. Also, there are a number of scripts to do this kind of admin stuff in the scripts section of this web site.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
-
Also do not forget to update statistics after truncating the tables.
.
-
Don't mean to be picky but have you thought of using the INFORMATION_SCHEMA.TABLES view to get the tables listing? This is based upon the sysobjects table but is the preferred method of accessing the system tables. It's supposed to put a layer of transparency between the programmer and the system tables.
Sean
-
We can use this sp to clear data in tables.
This sp will clear the foriegn key table first and followed by primary key table.
I have used Delete and Truncate statement to clear the data. Please refer SQL Books online difference of Delete and Truncate statement.
if exists (select * from sysobjects where id = object_id(N'[dbo].[ClearTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClearTableData]
GO
Create Proc ClearTableData
As
Declare @TableName Varchar(1000), @Reference Varchar(4), @SQL Varchar(8000), @ObjectName Varchar(8000),
@TabName Varchar(1000)
SET NOCOUNT ON
If db_name() In ('master', 'model', 'msdb', 'tempdb')
Begin
Print 'system database are not allowed to clear'
return 0
End
Set @ObjectName = ''
Set @TabName = ''
-- Delete data in Child Object and its Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select SO.Name "ChildObject",
Case When (Select Count(*) From SysReferences Where rkeyid = SO.id) = 0 Then 'N' Else 'Y' End As Ref
From SysObjects SO, SysReferences SR, SysObjects SO1
Where SO.id = SR.fkeyid And SO.xtype = 'U' And SO1.ID = SR.rkeyid
Order By SO1.Name Desc, SO.Name
OPEN ClearTableData_Cursor
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference
WHILE @@FETCH_STATUS = 0
BEGIN
If @Reference = 'Y'
Begin
-- Concatenate Primary Key Table name
Set @ObjectName = @ObjectName + ',' + @TableName
End
Else If @Reference = 'N'
Begin
Begin
-- Clear data from Foreign Key Tables(Child object)
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End
End
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference
END
CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
If Len(@Reference) > 0
Begin
-- Clear data for Concatenated Primary Key Tables
Set @ObjectName = Right(@ObjectName,Len(@ObjectName)-1)
While Len(@ObjectName) > 0
Begin
If (CHARINDEX(',',@ObjectName) > 0)
Begin
Set @TabName = Substring(@ObjectName, 1, CharIndex(',',@ObjectName)-1)
Set @ObjectName = SUBSTRING(@ObjectName, Len(@TabName) + 2, Len(@ObjectName))
End
Else
Begin
Set @TabName = @ObjectName
Set @ObjectName = ''
End
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TabName + ']'
exec (@SQL)
End
End
End
End
--Delete Data in Standalone Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id In (Select rkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN ClearTableData_Cursor
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TableName + ']'
exec (@SQL)
End
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END
CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
-- Delete data for BaseDate/Master Table
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN ClearTableData_Cursor
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End
FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END
CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
SET NOCOUNT OFF
[/code ends here]
Blog: Gulappa
-
You need just one line of code to clear all your user tbles.
exec sp_MSforeachtable 'truncate table ?'
-
What a top bit of code. I'm all for the simple option (sp_msforeachtable).
Sean
-
Actually when you look at the code of this procedure you will know why it is undocumented, but I agree that you won't get a 'shorter' solution to type.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Or you just generate the creation script of your database.
Then drop the DB and recreate it by applying the creation script.
This is the cleanest (and also the fastest) way to have a startup, ready to deploy database
Bye
Gabor
Bye
Gabor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply