March 20, 2006 at 6:28 am
Hi,
I've created a DTS package that does a lot of inserting records and updating records. After some research, I found some sources saying I could optimize things by deleting the indexes first prior to inserting records. Then recreating the indexes as a final step in my package.
This works well. But I find that sometimes, a step will fail if a I didn't recreate the constraint and/or index and I am trying to drop it.
Here is the code I use to drop the constraint and index
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [PK_MyTable]
DROP INDEX [dbo].[MyTable].[IX_MyTable_Last_Name]
I then recreate them again later. I have 5 constraints and 6 indexes that I am dropping and recreating. The tables I am using have over 4 - 9 million records. I use the "insert into" followed by a "select" statement.
The question: How do I test for the existence of a constraint? How do I test for the existence of an Index?
Many times, before I truncate a table, I test for the existence of the table. I've gone through books online and I didn't see any example of testing for a constraint and/or an index?
Thanks for the help?
Tony
Things will work out. Get back up, change some parameters and recode.
March 20, 2006 at 6:42 am
Tony
For constraints, if you know the name of the constraint:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'constraint_name')...
For indexes:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'index_name')...
John
March 20, 2006 at 7:50 am
John,
That worked beautifully!!
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply