Some time ago I was asked to truncate lots of tables in client's database.As such,all those tables have FK and PK dependency. You get the error like that if you run TRUNCATE TABLE Categories in Nortwind database as an example.
--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.
So , there is no such great problem as you know the database structure and can easily indetify parent-child tables. But what if you don't?
I'd like to show the function (sadly, I don't remember by whom it is written) that returns the tables ordered by its level. Level 0 means that it is refernced table and should be truncated last.
CREATE VIEW VFKs
AS
SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO
CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN
DECLARE @lvl AS INT
SET @lvl = 0
-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)
WHILE @@rowcount > 0
BEGIN
SET @lvl = @lvl + 1
-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)
END
SET @lvl = 0
-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'
RETURN
END
GO
Here's the results of an invocation of the function in Northwind:
SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl
table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2
Tuesday, August 14, 2007
Subscribe to:
Post Comments (Atom)
3 comments:
I used similar approach to truncate all the tables in a database
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx
Very nice script Madhivanan
Grandissimo...Bianco
Post a Comment