If you can decipher this as it is from some old ColdFusion code I wrote that
queries all the FK constraints from all database tables; disables then; deletes
data *removed for sake of sanity from this response*; then enables all the FK
constraints that were disabled.
It is a starting point :)
---- CODE BEGIN ----
<cfquery name="datatables_all" datasource="dsn">
SELECT TBLS.TABLE_NAME, CNSTS.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLES TBLS INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CNSTS ON TBLS.TABLE_NAME =
CNSTS.TABLE_NAME
WHERE
TBLS.Table_Type = 'BASE TABLE'
AND
LEFT(CNSTS.CONSTRAINT_NAME, 3) = 'FK_'
GROUP BY TBLS.TABLE_NAME, CNSTS.CONSTRAINT_NAME
ORDER BY TBLS.table_name, CNSTS.CONSTRAINT_NAME
</cfquery>
<!--- DISABLE THE FK CONSTRAINTS --->
<cfoutput query="datatables_all" group="TABLE_NAME">
<cftransaction> <pre>[1a]</pre>
<cfoutput>
<cfquery name="lpDisableConstraint" datasource="dsn">
ALTER TABLE #datatables_all.Table_Name[datatables_all.currentrow]#
NOCHECK CONSTRAINT #datatables_all.Constraint_Name[datatables_all.currentrow]#
</cfquery>
</cfoutput>
</cftransaction>
</cfoutput>
<!--- DO DELETE OF DATA HERE --->
<!--- DO DELETE OF DATA HERE --->
<!--- DO DELETE OF DATA HERE --->
<!--- EABLE THE FK CONSTRAINTS --->
<cfoutput query="datatables_all" group="TABLE_NAME">
<cftransaction> <pre>[1b]</pre>
<cfoutput>
<cfquery name="lpDisableConstraint" datasource="dsn">
ALTER TABLE #datatables_all.Table_Name[datatables_all.currentrow]#
CHECK CONSTRAINT #datatables_all.Constraint_Name[datatables_all.currentrow]#
</cfquery>
</cfoutput>
</cftransaction>
</cfoutput>
---- CODE END ----
HTH,
Brad
-----Original Message-----
From: Bruce, Rodney S CTR USA AMC [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 12, 2007 4:28 PM
To: SQL
Subject: Table constraints
Hi,
I am trying to create a stored procedure to delete all data from the tables in
a test database.
So we can fill it with a data set, run tests and load another set of data....
Deleting the data itself is not an issue. But we have many relationships
between tables. So just doing a loop over the tables to delete the data gets
constraint errors. Its not a big deal to run the SP a few times, but I was
wondering if there was a way to query for the relations, delete/suspend them
and then add them back?
So far my SP looks like:
ALTER PROCEDURE [dbo].[SP_DataDump]
@Tblname varchar(250)
AS
DECLARE @tableName varchar(250)
DECLARE @newSQL varchar(1000)
IF (@Tblname = 'all')
BEGIN
DECLARE tables_csr cursor for
Select name
From sysObjects
Where type = 'U' and Patindex('%_LU_%', name ) = 0
END
ELSE
BEGIN
DECLARE tables_csr cursor for
Select name
From sysObjects
Where type = 'U' and Patindex('%'[EMAIL PROTECTED] '%', name )
> 0
END
Open tables_csr
fetch next from tables_csr into @tableName
while @@fetch_status = 0
Begin
SET @newSQL = 'Delete From ' + @tablename
exec(@newSQL)
fetch next from tables_csr into @tableName
end
_LU_ are our "LOOKUP" tables like states etc, data never changes so
don't need/want to empty them.
I know a similar statement to this can add/remove the constraints. I
just don't know how/if possible to get the constraints for each table in
the sp.
ALTER TABLE [dbo].[Components_tbl] WITH CHECK ADD CONSTRAINT
[FK_Components_tbl_Image_tbl] FOREIGN KEY([ImageID])
REFERENCES [dbo].[Image_tbl] ([ImageID])
Any help would be appreciated.
Rodney
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2878
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
