December 15, 2011 at 8:53 am
Hi,
I want to empty the database (only data). Is there any script/SP available to that.
SQL Server 2005 version
Thanks
December 15, 2011 at 8:57 am
What does empty mean? Does that mean you want to export the data or you want to delete all the data?
December 15, 2011 at 9:03 am
If you are looking to delete (or truncate) all tables take a look at the undocumented stored procedure "sp_MSforeachtable". You should be able to plug that into your search engine of choice and get plenty of examples pretty easily.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 15, 2011 at 9:09 am
here's away that takes foreign keys into consideration; with MsForEachTable, you get errors in deleting due to foreign key violations.
it also has the ability to add tables you want to skip, like specific lookup/setup tables.
nocount on
CREATE TABLE #Skipme(TableName varchar(255))
INSERT INTO #Skipme
SELECT 'tbCity' UNION ALL
SELECT 'tbState' UNION ALL
SELECT 'tbCounty' UNION ALL
SELECT 'OtherLookupTables'
INSERT INTO #Skipme
SELECT name from sys.objects
where LEFT(name,2) = 'LU'
OR LEFT(name,2) = 'TB'
declare @level tinyint
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level tinyint not null)
insert into #tables (id, TableName, Level)
select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0
from sysobjects where xtype = 'U' and status > 0
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName
from #tables
where level = 0
And TableName Not In (SELECT TableName from #Skipme)
select 'DELETE ' + TableName
from #tables
where level > 0
And TableName Not In (SELECT TableName from #Skipme)
order by level
drop table #tables
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply