NuGet Gallery package deletion scripts

delete-multiple-nuget-packages.sql

-- Delete packages with ids that start with a specific string
DECLARE @PackageRegistrationKeys table([Key] int)
INSERT INTO @PackageRegistrationKeys
SELECT [Key] FROM [NuGetGallery].[dbo].[PackageRegistrations]
WHERE [Id] LIKE 'Example.Nasty.Package.%'

BEGIN TRANSACTION
DELETE pf
 FROM [NuGetGallery].[dbo].[PackageFrameworks] pf
 JOIN [NuGetGallery].[dbo].[Packages] p ON pf.[Package_Key] = p.[Key]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE pa
 FROM [NuGetGallery].[dbo].[PackageAuthors] pa
 JOIN [NuGetGallery].[dbo].[Packages] p ON pa.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE gs
 FROM [NuGetGallery].[dbo].[GallerySettings] gs
 JOIN [NuGetGallery].[dbo].[PackageStatistics] ps ON gs.[DownloadStatsLastAggregatedId] = ps.[Key]
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE ps
 FROM [NuGetGallery].[dbo].[PackageStatistics] ps
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE pd
 FROM [NuGetGallery].[dbo].[PackageDependencies] pd
 JOIN [NuGetGallery].[dbo].[Packages] p ON pd.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE
 FROM [NuGetGallery].[dbo].[Packages]
 WHERE [PackageRegistrationKey] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE por
 FROM [NuGetGallery].[dbo].[PackageOwnerRequests] por
 JOIN [NuGetGallery].[dbo].[PackageRegistrations] pr ON pr.[Key] = por.[PackageRegistrationKey]
 WHERE pr.[Key] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE pro
 FROM [NuGetGallery].[dbo].[PackageRegistrationOwners] pro
 JOIN [NuGetGallery].[dbo].[PackageRegistrations] pr ON pr.[Key] = pro.[PackageRegistrationKey]
 WHERE pr.[Key] IN (SELECT [Key] FROM @PackageRegistrationKeys)
DELETE FROM [NuGetGallery].[dbo].[PackageRegistrations]
 WHERE [Key] IN (SELECT [Key] FROM @PackageRegistrationKeys)
COMMIT TRANSACTION

delete-nuget-package-version.sql

-- Delete a specific version of a package with a specific package id
DECLARE @PackageKey int
SELECT @PackageKey = p.[Key]
 FROM [Packages] p
 JOIN [PackageRegistrations] pr ON pr.[Key] = p.[PackageRegistrationKey]
 WHERE pr.[Id] = 'Example.Nasty.Package'
 AND [Version] = '9.9.9'

BEGIN TRANSACTION
DELETE pf
 FROM [NuGetGallery].[dbo].[PackageFrameworks] pf
 JOIN [NuGetGallery].[dbo].[Packages] p ON pf.[Package_Key] = p.[Key]
 WHERE p.[Key] = @PackageKey
DELETE pa
 FROM [NuGetGallery].[dbo].[PackageAuthors] pa
 JOIN [NuGetGallery].[dbo].[Packages] p ON pa.[PackageKey] = p.[Key]
 WHERE p.[Key] = @PackageKey
DELETE gs
 FROM [NuGetGallery].[dbo].[GallerySettings] gs
 JOIN [NuGetGallery].[dbo].[PackageStatistics] ps ON gs.[DownloadStatsLastAggregatedId] = ps.[Key]
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE p.[Key] = @PackageKey
DELETE ps
 FROM [NuGetGallery].[dbo].[PackageStatistics] ps
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE p.[Key] = @PackageKey
DELETE pd
 FROM [NuGetGallery].[dbo].[PackageDependencies] pd
 JOIN [NuGetGallery].[dbo].[Packages] p ON pd.[PackageKey] = p.[Key]
 WHERE p.[Key] = @PackageKey
DELETE
 FROM [NuGetGallery].[dbo].[Packages]
 WHERE [Key] = @PackageKey
COMMIT TRANSACTION

delete-nuget-package.sql

-- Delete all versions of a package with a specific package id
DECLARE @PackageRegistrationKey int
SELECT @PackageRegistrationKey = [Key]
 FROM [PackageRegistrations]
 WHERE [Id] = 'Example.Nasty.Package'

BEGIN TRANSACTION
DELETE pf
 FROM [NuGetGallery].[dbo].[PackageFrameworks] pf
 JOIN [NuGetGallery].[dbo].[Packages] p ON pf.[Package_Key] = p.[Key]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE pa
 FROM [NuGetGallery].[dbo].[PackageAuthors] pa
 JOIN [NuGetGallery].[dbo].[Packages] p ON pa.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE gs
 FROM [NuGetGallery].[dbo].[GallerySettings] gs
 JOIN [NuGetGallery].[dbo].[PackageStatistics] ps ON gs.[DownloadStatsLastAggregatedId] = ps.[Key]
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE ps
 FROM [NuGetGallery].[dbo].[PackageStatistics] ps
 JOIN [NuGetGallery].[dbo].[Packages] p ON ps.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE pd
 FROM [NuGetGallery].[dbo].[PackageDependencies] pd
 JOIN [NuGetGallery].[dbo].[Packages] p ON pd.[PackageKey] = p.[Key]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE
 FROM [NuGetGallery].[dbo].[Packages]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE por
 FROM [NuGetGallery].[dbo].[PackageOwnerRequests] por
 JOIN [NuGetGallery].[dbo].[PackageRegistrations] pr ON pr.[Key] = por.[PackageRegistrationKey]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE pro
 FROM [NuGetGallery].[dbo].[PackageRegistrationOwners] pro
 JOIN [NuGetGallery].[dbo].[PackageRegistrations] pr ON pr.[Key] = pro.[PackageRegistrationKey]
 WHERE [PackageRegistrationKey] = @PackageRegistrationKey
DELETE FROM [NuGetGallery].[dbo].[PackageRegistrations]
 WHERE [Key] = @PackageRegistrationKey
COMMIT TRANSACTION