I am trying to write a script that will strip all indexes ( including PKs ) from a database.
So far I have this :
ALTER DATABASE membership SET AUTO_CREATE_STATISTICS OFF
go
DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)
DECLARE [indexes] CURSOR FOR
SELECT [sysindexes].[name] AS [Index],
[sysobjects].[name] AS [Table]
FROM [sysindexes]
INNER JOIN [sysobjects]
ON [sysindexes].[id] = [sysobjects].[id]
WHERE [sysindexes].[name] IS NOT NULL
AND [sysobjects].[type] = 'U'
OPEN [indexes]
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END
CLOSE [indexes]
DEALLOCATE [indexes]
which runs without error, but does not drop all the indexes, even though the Message window clearly shows a drop command has been run :
DROP INDEX [CPK_STATEH] ON [statehouse]
I also see drop commands for 'on the fly' indexes that were created with auto-statistics
DROP INDEX [_WA_Sys_cClass_02B25B50] ON [MembershipClasses]
I can't seem to see a pattern as to which indexes are dropped and which aren't, but if I run any single line of the drop index commands shown in messages they seem to work fine.
I'm clearly over my head here in TSQL. Would appreciate any guidance - or even better a script that will do exactly what I want - dropping all indexes, keys, triggers, constraints etc. ( I am then turning my SMO tool loose on the database and altering the structure prior to some post-SMO script driven munging that will use the current UID fk/pk system to create new pk/fk s as integers and then SSIS the data into another database that uses the new structure and lets the guids go )
The entire point of what I am doing is to add an integer field for every UID field used as a key, use identity to create PK integers, then use something like this
UPDATE dbo.Donations
SET iFK_Members = m.ipk_members
FROM dbo.Members AS m
INNER JOIN dbo.Donations AS d
ON m.cPK_Members=d.cFK_Members
to get the integer foreign keys to contain the correct values
Appreciate any help you can offer.
TIA
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.