Why not just drop them and then recreate them? EM makes it easy to make the scripts.
~Terry -----Original Message----- From: Bruce, Rodney S CTR USA AMC [mailto:[EMAIL PROTECTED] Sent: Thursday, July 12, 2007 18:28 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2880 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
