Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Drop all indexes tsql
Message
 
 
À
14/01/2010 09:17:58
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2008
Divers
Thread ID:
01443926
Message ID:
01443930
Vues:
64
This message has been marked as the solution to the initial question of the thread.
In your script I don't see the actual DROP command, I only see PRINT command.

>I am trying to write a script that will strip all indexes ( including PKs ) from a database.
>
>So far I have this :
>
>
>/* http://refactormycode.com/codes/522-drop-all-indexes-in-sql-server
>Script to Drop all indexes in a database
>*/
>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
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform