views:

118

answers:

1

I am trying to restore a backup of a Microsoft Dynamics NAV database, which unfortunately fails as it tries to set a CLUSTERED KEY for the tables which already have clustered keys.

In NAV, every company in the database gets its own copy of the tables, prefixed with the Company's name, e.g. COMPANY$User_Setup. I'd therefore like to remove any clustered key on a given company, which means on any table which name starts with 'Company$'.

Has anybody got a SQL statement that could perform this?

Thanks in advance!

Best regards, Martin

+1  A: 

You'll need to do it as a cursor. Assuming each PK constraint is named consistantly and is based on the table name, you'd be able to do something like (untested, so may contain typos or vauge syntax errors):

DECLARE mycursor CURSOR FOR SELECT name FROM sysobjects WHERE name LIKE 'Company$%'

OPEN CURSOR
FETCH NEXT FROM mycursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER TABLE QUOTENAME(' + @tablename + ') DROP CONSTRAINT PK_' + @tablename 
    EXEC sp_ExecuteSQL @sql
    FETCH NEXT FROM mycursor INTO @tablename
END

CLOSE CURSOR
DEALLOCATE CURSOR

If your PK's aren't named based on tablename, then you'll have to modify this to also query based on sysconstraints or sysindexes to get the actual PK name.

Chris J
That helps, thanks. I think, I can start on this one.
Martin C.
That won't do it - you're just removing the Primary Key constraint - that's not dropping the index yet. Furthermore, while the primary key typically is the clustered index by default, this is not a requirement - it could be two different indices. And even more: the primary key (if it's the clustered index as well) usually also is referenced by foreign key relationships - so those would have to be dropped, too, before you can drop the primary key index.
marc_s
Normally dropping the PK constraint will also drop the associated index. However, I admit to making some assumptions for keeping it simple ... but to illustrate the basis of a solution, it does provide a starting point.
Chris J
Thanks, I was aware I'd be dropping the primary key. In fact, I'd just reset CLUSTERED to HEAP on the index. Is this possible somehow?
Martin C.
No - you'd have to drop and recreate the index.If the columns are the same in all tables (which it sounds like it is as you've said they get a copy of tables), then you can just extend the cursor loop to create the non-clustered (unique?) index on-the-fly after it's dropped the constraint.
Chris J