I needed a way to do them on the fly. If one was created, I needed to
be able to get the new constraint without having to create a new script.
I knew the information was in the sys tables, just not how to pull it
out.
Brad Comer gave some CF code I converted to a SP and it works great.
My SP looks like this:
------------------------------Start
Code-------------------------------------------------
ALTER PROCEDURE [dbo].[SP_DataDump]
--The table(s) to have data deleted
@Tblname varchar(250)
AS
DECLARE @tableName varchar(250)
DECLARE @ContblName varchar(250)
DECLARE @Consname varchar(300)
DECLARE @newSQL varchar(1000)
DECLARE @newSQL2 varchar(1000)
-- Finds the contraints for all tables in the database
DECLARE CONSTRAINT_CSR cursor for
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
--Loops thru the contraints and turns them off/set to not check
--All need to be done, even if the data from a table is not be deleted
below.
--It may have a contraint on a table who's data is being deleted.
Open CONSTRAINT_CSR
fetch next from CONSTRAINT_CSR into @ContblName, @Consname
while @@fetch_status = 0
Begin
SET @newSQL2 = 'ALTER TABLE '+ @ContblName + ' NOCHECK
CONSTRAINT ' + @Consname
Exec (@newSQL2)
fetch next from CONSTRAINT_CSR into @ContblName,
@Consname
End
close CONSTRAINT_CSR
-- Gets the tables who's data will be deleted
IF (@Tblname = 'all' or @Tblname IS NULL)
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
--Loops thru the tables and deletes data
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
close tables_csr
deallocate tables_csr
--Loops back over all table contraints and resets them
Open CONSTRAINT_CSR
fetch next from CONSTRAINT_CSR into @ContblName, @Consname
while @@fetch_status = 0
Begin
SET @newSQL2 = 'ALTER TABLE '+ @ContblName + ' CHECK CONSTRAINT
' + @Consname
Exec (@newSQL2)
fetch next from CONSTRAINT_CSR into @ContblName, @Consname
End
close CONSTRAINT_CSR
deallocate CONSTRAINT_CSR
-----------------------------End
Code--------------------------------------------------------
This works, but if there are other ways I am interested in learning them
also.
Thanks
Rodney
-----Original Message-----
From: Bader, Terrence C CTR MARMC, 231
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 17, 2007 11:06 AM
To: SQL
Subject: RE: Table constraints
Why not just drop them and then recreate them? EM makes it easy to make
the scripts.
~Terry
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2881
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
