September 15, 2011 at 7:08 am
can any one give syntax of deleting the data in all the tables in a database in sql 2008 with out deleting the database
September 15, 2011 at 7:17 am
SELECT 'delete from ' + name + ';'
FROM sys.tables
WHERE type = 'U'
Copy-and-paste the results into a connection window and run them.
Or use that for a cursor and execute the commands as dynamic SQL, but that's more work. Worthwhile if you need to do this frequently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2011 at 7:19 am
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
September 15, 2011 at 7:20 am
YTZ (9/15/2011)
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
Okay, I have to ask, how will that delete any data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2011 at 7:25 am
YTZ (9/15/2011)
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
Hmm, seem to have missed a bit?
USE databasename
GO
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)'
September 15, 2011 at 7:39 am
yeah without taking Foreign keys into consideration, doing a straight DELETE will typically raise errors .
this snippet generates teh TRUNCATE TABLE/DELETE FROM statements in Foreign key hierarchy Order:
that avoids issues where the FK's are no longer trusted because you disabled the constriants as well ,
and finally, there are always some tables that you don't want to delete from; this adds the ability to exclude specific tables.
SET NOCOUNT ON
--the list of tables we do not want to touch.
CREATE TABLE #Skipme(SchemaName varchar(255),TableName varchar(255))
INSERT INTO #Skipme
SELECT 'dbo','tbCity' UNION ALL
SELECT 'dbo','tbState' UNION ALL
SELECT 'dbo','tbCounty' UNION ALL
SELECT 'dbo','OtherLookupTables'
--adding some more based on a known factor: my lookup tables all start with LU* or TB*
INSERT INTO #Skipme
SELECT schema_name(schema_id),name FROM sys.objects
WHERE LEFT(name,2) = 'LU'
OR LEFT(name,2) = 'TB'
--get the list of tables, and put them in FK hierarchy order.
CREATE TABLE #MyObjectHierarchy (
HID int identity(1,1) NOT NULL PRIMARY KEY,
ObjectId int,
ObjectFullName varchar(510),
ObjectType int,
TypeDesc AS CASE
WHEN ObjectType = 1 THEN 'FUNCTION'
WHEN ObjectType = 4 THEN 'VIEW'
WHEN ObjectType = 8 THEN 'TABLE'
WHEN ObjectType = 16 THEN 'PROCEDURE'
WHEN ObjectType =128 THEN 'RULE'
ELSE ''
END,
ObjectSchema varchar(255),
ObjectName varchar(255),
DependencyOrder int)
--our list of objects in dependancy order
INSERT #MyObjectHierarchy (ObjectType,ObjectName,ObjectSchema,DependencyOrder)
EXEC sp_msdependencies @intrans = 1
--Now simply select our results based on the joins of these two tables.
--the join excluses tables in out #SkipMe table
SELECT --*, --uncomment to see the full results.
CASE
WHEN DependencyOrder <= 1 --no dependancies at all
THEN 'TRUNCATE TABLE ' + QUOTENAME(T.ObjectSchema) + '.' + QUOTENAME(T.ObjectName)
ELSE 'DELETE FROM ' + QUOTENAME(T.ObjectSchema) + '.' + QUOTENAME(T.ObjectName)
END as cmd
FROM #MyObjectHierarchy T
LEFT OUTER JOIN #Skipme S
ON T.ObjectSchema = S.SchemaName
AND T.ObjectName = S.TableName
WHERE S.TableName IS NULL --not in the join
AND T.ObjectType = 8 --only tables
ORDER BY DependencyOrder,HID
--cleanup our temp tables
DROP TABLE #SkipMe
DROP TABLE #MyObjectHierarchy
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply