August 18, 2011 at 9:42 pm
I have to truncate some of the tables in a database.
But because these tables have foreign key constraints, I cannot drop them.
Is there a way to generate script of the create and drop foreign keys of some given tables?
So that I will first drop the foreign keys, truncate the tables, and then add the forign keysback.
August 19, 2011 at 3:50 am
You can always script out your constraints through wizard. Whats the difficulty you facing here?
August 19, 2011 at 8:44 am
If what you are trying to do is delete all the data and reset the identity you can do without dropping your foreign keys. As you have discovered you can't truncate but what you can do is:
delete [tablename]
DBCC CHECKIDENT ([tablename], RESEED ,0)
Of course you should make sure that you don't orphan any data in other tables.
_______________________________________________________________
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/
August 19, 2011 at 8:58 am
here's a snippet based on Seans example, that gets all the tables in FK hierarchy order and generates the Truncates/delete commands for you, so you don't have to drop and recreate the constraints.
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]'
INSERT INTO #Skipme
SELECT QUOTENAME(SCHEMA_NAME(SCHEMA_ID)),
QUOTENAME(name) FROM sys.tables
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,
SchemaName VARCHAR(255),
TableName VARCHAR(255) NOT NULL,
LEVEL TINYINT NOT NULL)
INSERT INTO #tables (id, SchemaName,TableName, LEVEL)
SELECT
OBJECT_ID,
QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) ,
QUOTENAME(name) AS TableName, 0
FROM sys.tables WHERE is_ms_shipped=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 ' + SchemaName + '.' + TableName
FROM #tables
WHERE LEVEL = 0
AND TableName NOT IN (SELECT TableName FROM #Skipme)
SELECT 'DELETE ' + SchemaName + '.' + TableName + '; DBCC CHECKIDENT (' + SchemaName + '.' + TableName + ', RESEED ,0)'
FROM #tables
WHERE LEVEL > 0
AND TableName NOT IN (SELECT TableName FROM #Skipme)
ORDER BY LEVEL
DROP TABLE #tables
DROP TABLE #Skipme
Lowell
August 19, 2011 at 9:26 am
So in the result of the above script, does it mean the tables in truncate statement mean they don't have forign key constraint, and the tables in delete statement are those have forign key constraints?
Thanks
August 19, 2011 at 9:33 am
sqlfriends (8/19/2011)
So in the result of the above script, does it mean the tables in truncate statement mean they don't have forign key constraint, and the tables in delete statement are those have forign key constraints?Thanks
that is exactly correct.
by scanning the FK relationships, anything with no foreign keys can be truncated. and anything with a FK would need to be deleted, and potentially reseeded.
Lowell
August 22, 2012 at 8:46 am
Lowell (8/19/2011)
here's a snippet based on Seans example, that gets all the tables in FK hierarchy order and generates the Truncates/delete commands for you, so you don't have to drop and recreate the constraints.
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]'
INSERT INTO #Skipme
SELECT QUOTENAME(SCHEMA_NAME(SCHEMA_ID)),
QUOTENAME(name) FROM sys.tables
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,
SchemaName VARCHAR(255),
TableName VARCHAR(255) NOT NULL,
LEVEL TINYINT NOT NULL)
INSERT INTO #tables (id, SchemaName,TableName, LEVEL)
SELECT
OBJECT_ID,
QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) ,
QUOTENAME(name) AS TableName, 0
FROM sys.tables WHERE is_ms_shipped=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 ' + SchemaName + '.' + TableName
FROM #tables
WHERE LEVEL = 0
AND TableName NOT IN (SELECT TableName FROM #Skipme)
SELECT 'DELETE ' + SchemaName + '.' + TableName + '; DBCC CHECKIDENT (' + SchemaName + '.' + TableName + ', RESEED ,0)'
FROM #tables
WHERE LEVEL > 0
AND TableName NOT IN (SELECT TableName FROM #Skipme)
ORDER BY LEVEL
DROP TABLE #tables
DROP TABLE #Skipme
Hi - I've just been playing around with this script & there's a slight error, I think.
The DBCC CHECKIDENT (schema.tablename, RESEED ,0) syntax produced by the script is invalid. Instead, using
DBCC CHECKIDENT ("schema.tablename", RESEED ,0)
(note double quotes) seems to work fine:
SELECT 'DELETE ' + SchemaName + '.' + TableName + '; DBCC CHECKIDENT ("' + SchemaName + '.' + TableName + '", RESEED ,0)'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply