/*
 * $Id: migprep70.sql 84480 2007-07-24 06:34:27Z pca $
 *
 * Prepare to migrate a ManageSoft 6.6 database into ManageSoft 7.0 format.
 *
 * Use this first, then schema.sql, OpQueries.sql, then migpost.sql, and finally
 * install Managesoft 7.0 which will create the stored procedures.
 *
 * The aim here is to make minimal changes that will allow schema.sql
 * to succeed in creating any new objects, before migpost.sql finishes
 * reshaping some tables and dropping any unused objects.
 *
 * Copyright 1995-2003 ManageSoft Corporation Ltd ABN 40 052 412 156. All rights reserved.
 */

USE ManageSoft
GO

--
-- Create tables the have lists of the tables, views and stored procedures from the ManageSoft product
--
IF EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ManageSoftMigration'
)
BEGIN
	TRUNCATE TABLE ManageSoftMigration 
	DROP TABLE ManageSoftMigration 
END
CREATE TABLE ManageSoftMigration (
		-- Name of table/stored procedure/view
		[Name]	nvarchar(128),

		-- Type of object - table, procedure or view
		Type	nvarchar(32)
	);
GO
-- TABLES
INSERT INTO ManageSoftMigration VALUES ('Action', 'table');
INSERT INTO ManageSoftMigration VALUES ('ActionApplies', 'table');
INSERT INTO ManageSoftMigration VALUES ('ActionState', 'table');
INSERT INTO ManageSoftMigration VALUES ('ADComputer', 'table');
INSERT INTO ManageSoftMigration VALUES ('ADSDOU', 'table');
INSERT INTO ManageSoftMigration VALUES ('ADUser', 'table');
INSERT INTO ManageSoftMigration VALUES ('Computer', 'table');
INSERT INTO ManageSoftMigration VALUES ('ComputerUsage', 'table');
INSERT INTO ManageSoftMigration VALUES ('DatabaseConfiguration', 'table');
INSERT INTO ManageSoftMigration VALUES ('DistributedPackage', 'table');
INSERT INTO ManageSoftMigration VALUES ('DistributionGroup', 'table');
INSERT INTO ManageSoftMigration VALUES ('DistributionGroupMember', 'table');
INSERT INTO ManageSoftMigration VALUES ('DistributionServer', 'table');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerType', 'table');
INSERT INTO ManageSoftMigration VALUES ('Domain', 'table');
INSERT INTO ManageSoftMigration VALUES ('HardwareObject', 'table');
INSERT INTO ManageSoftMigration VALUES ('HardwareProperty', 'table');
INSERT INTO ManageSoftMigration VALUES ('InstallationHistory', 'table');
INSERT INTO ManageSoftMigration VALUES ('InstallationNotification', 'table');
INSERT INTO ManageSoftMigration VALUES ('InstallationStatus', 'table');
INSERT INTO ManageSoftMigration VALUES ('InventoryReport', 'table');
INSERT INTO ManageSoftMigration VALUES ('Job', 'table');
INSERT INTO ManageSoftMigration VALUES ('LicenseAllocation', 'table');
INSERT INTO ManageSoftMigration VALUES ('LicenseModel', 'table');
INSERT INTO ManageSoftMigration VALUES ('NetworkDevice', 'table');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredBy', 'table');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystem', 'table');
INSERT INTO ManageSoftMigration VALUES ('Organization', 'table');
INSERT INTO ManageSoftMigration VALUES ('PackageFamily', 'table');
INSERT INTO ManageSoftMigration VALUES ('PackageProvides', 'table');
INSERT INTO ManageSoftMigration VALUES ('PackageRequires', 'table');
INSERT INTO ManageSoftMigration VALUES ('PackageVersion', 'table');
INSERT INTO ManageSoftMigration VALUES ('Policy', 'table');
INSERT INTO ManageSoftMigration VALUES ('PolicyApplies', 'table');
INSERT INTO ManageSoftMigration VALUES ('ProductContainsSoftware', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareFile', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareOccurrence', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareProduct', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePublisher', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareReseller', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareUsagePerWeek', 'table');
INSERT INTO ManageSoftMigration VALUES ('SoftwareVersion', 'table');
INSERT INTO ManageSoftMigration VALUES ('TargetedPackage', 'table');
INSERT INTO ManageSoftMigration VALUES ('TargetType', 'table');
INSERT INTO ManageSoftMigration VALUES ('Task', 'table');
INSERT INTO ManageSoftMigration VALUES ('TaskSchedule', 'table');
INSERT INTO ManageSoftMigration VALUES ('TaskType', 'table');
INSERT INTO ManageSoftMigration VALUES ('UnknownFile', 'table');
INSERT INTO ManageSoftMigration VALUES ('User', 'table');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANDistributionJob', 'table');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANStatus', 'table');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANTask', 'table');
-- PROCEDURES
INSERT INTO ManageSoftMigration VALUES ('ActionAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetAllByActionUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetAllByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetByActionUIDComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetByActionUIDDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetByActionUIDDNSName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetByActionUIDIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesGetByActionUIDMACAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveAllByActionUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveAllByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveByActionUIDComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveByActionUIDDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveByActionUIDDNSName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveByActionUIDIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesRemoveByActionUIDMACAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesReplaceSubsetByActionUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesReplaceSubsetByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateBatchByActionUIDComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateBatchByActionUIDDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateBatchByActionUIDDNSName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateBatchByActionUIDIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateBatchByActionUIDMACAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateByActionUIDComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateByActionUIDDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateByActionUIDDNSName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateByActionUIDIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionAppliesUpdateByActionUIDMACAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionGetAllByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionGetByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionGetByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionRemoveAllByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionRemoveByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionRemoveByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionReplaceSubsetByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionStateBind', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionStateGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionStateGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionStateGetByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionUpdateBatchByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionUpdateBatchByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionUpdateByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ActionUpdateByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AddNetworkDevice', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ADLoadDomain', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ADReconcileAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUCountDaysG', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUCountMdG', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUSessions', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUSessionsOnComp', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUSessionsSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('AUSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('Auto_Config_Complete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('BindToComputerAndUser', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('CachePackageInsert', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('CachePackageSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('CachePackageSync', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ClearNetworkDevices', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('CompUsage', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('Config_Complete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('Config_Start', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('CopyPermission', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DeleteAllocation', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DeleteComputer', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DeleteOrganization', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DeleteUser', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DepLoc_Delete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistGroup_Create', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistGroup_Delete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistGroup_Rename', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistGroupMembership_Create', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistGroupMembership_Delete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributedPackage_Complete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributedPackage_Start', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerGetByServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerRemoveByServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerTypeGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerTypeGetByTargetTypeID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerTypeGetByTargetTypeName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerUpdateBatchByServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DistributionServerUpdateByServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DomainComputerPopulate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DomainCreate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DomainOrganizationPopulate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DomainRemove', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('DomainUserPopulate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('EmptyPolicyUsers', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('FindServerOrLoc', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('FixOUReferences', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobGetAllByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobGetByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobGetByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobRemoveAllByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobRemoveByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobRemoveByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobReplaceSubsetByTaskID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobUpdateBatchByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobUpdateBatchByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobUpdateByTaskIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('JobUpdateByUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('LinkNetworkDevice', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ListDomains', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAddWakeOnLANJob', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAddWakeOnLANStatus', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAddWakeOnLANTask', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAgedManagedDevices', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAllocationDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAllSoftwareProductSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsAllSoftwareVersions', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsContainedSoftware', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDistParentName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDLDistributionDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDLDistributionSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDLDistributionSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDSDistributionConfig', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDSDistributionDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDSDistributionSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsDSDistributionSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsEditAllocations', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsGetWakeOnLANHostInfo', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsHWClassesInventorySummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsHWClassManagedDevicesSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsListModels', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsListPublishers', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsListResellers', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsListTracking', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsManagedDeviceHWClassDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsManagedDeviceHWClassesSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsManagedDevicesInventorySummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsManagedDeviceSWInventoryDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsOSBreakdown', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsOUAllocations', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsOUDNtoOUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsOUsSoftwareProductAllocation', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsPkgDistributionDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsPkgsDistributionSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsPkgsDistributionSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsRemoveWakeOnLANTask', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsROAllSoftwareProductSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSoftwareProductAllocationBreachSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSoftwareProductAllocationSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSoftwareProductAllocationSummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSoftwareProductUsageDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSWInventoryDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSWInventoryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSWInventorySummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsSWVersionsInventorySummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsUpdateWakeOnLANJob', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsUpdateWakeOnLANStatus', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsUsersSWInventorySummaryList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('mgsUserSWInventoryDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('MoveComputerToNewOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('MoveUserToNewOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByGetAllByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByGetByDeviceIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByRemoveAllByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByRemoveByDeviceIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByReplaceSubsetByDeviceID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredBySchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByUpdateBatchByDeviceIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceDiscoveredByUpdateByDeviceIDServerUID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByDeviceName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByDuplicateID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByNTDomainName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetAllByPhysicalAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceGetTargetedByPolicy', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByComputerID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByDeviceName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByDuplicateID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByIPAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByNTDomainName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveAllByPhysicalAddress', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NetworkDeviceUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('NewEmptyDistServer', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ObsoleteAUData', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OperatingSystemUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OrganizationCreate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('OrganizationDNCreate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageProvidesGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageProvidesRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageProvidesUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageProvidesUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresGetAllRequiredBy', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresGetAllRequiredByRequiredObject', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresRemoveAllByRequiredBy', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageRequiresUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageVersionList', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageVersionUpdate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PackageVersionSet', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyAppliesReplaceSubsetByPolicyDomainID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyAppliesSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyDistributeCount', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyDistributeQuery', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyInsertNew', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyInstallationUpdate', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyRemoveOld', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyUpdateBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyUpdateChanged', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyUpdateDiagnostics', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PolicyUpdatePolicies', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PopulateComputerGUIDs', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PopulateMissingOUs', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PopulateNetworkDevices', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PopulateOrganizationGUIDs', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('PopulateUserGUIDs', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReconcileComputers', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReconcileDomain', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReconcileDomainNow', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReconcileOrganizations', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReconcileUsers', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RemoteExec_Complete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RemovePackage_Complete', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RemovePackage_Start', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RenameComputer', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RenameOrganization', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('RenameUser', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('Resync_Clear', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('Resync_State', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('ReverseDN', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SecPatch_PatchUpdateBatchByPatchNameBulletinIDPatchLocationIDSQNumber', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SecPatch_PatchUpdateByPatchNameBulletinIDPatchLocationIDSQNumber', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SecurityPatchInstallationFailure', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationDetailComputerExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationDetailComputerMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationDetailUserExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationDetailUserMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationFailure', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationNonPolicyComputerExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationNonPolicyComputerMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationNonPolicyUserExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationNonPolicyUserMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationPending', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationPendingComputerExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationPendingComputerMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationPendingUserExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationPendingUserMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationSuccess', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationSummary', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationSummaryExactOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwarePackageInstallationSummaryMatchOU', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('SoftwareUsagePerWeekSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TargetedPolicyOUs', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskBind', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskGetByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskRemoveByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleAdd', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleAddBatch', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleRemoveAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleRemoveByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskScheduleUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskSchema', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskTypeBind', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskTypeGetAll', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskTypeGetByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskTypeGetByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskUpdateBatchByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskUpdateBatchByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskUpdateByID', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('TaskUpdateByName', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('UpdateInstallation', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('UpdateNetworkDevices', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('UpdateSoftwareVersion', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('UpdateUsageData', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANEffectivePolicy', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANTaskDetails', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANTaskDetailsGraph', 'procedure');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANTasks', 'procedure');
-- VIEWS
INSERT INTO ManageSoftMigration VALUES ('ComputerWithDN', 'view');
INSERT INTO ManageSoftMigration VALUES ('SDOU', 'view');
INSERT INTO ManageSoftMigration VALUES ('sysconstraints', 'view');
INSERT INTO ManageSoftMigration VALUES ('syssegments', 'view');
INSERT INTO ManageSoftMigration VALUES ('WakeOnLANHostInfo', 'view');
-- TRIGGERS
INSERT INTO ManageSoftMigration VALUES ('ReverseOrganizationDN', 'trigger');
INSERT INTO ManageSoftMigration VALUES ('ReverseDomainDN', 'trigger');
INSERT INTO ManageSoftMigration VALUES ('PackageNameCreate', 'trigger');
INSERT INTO ManageSoftMigration VALUES ('PackageVersionUpdateVersion', 'trigger');
INSERT INTO ManageSoftMigration VALUES ('PackageVersionDelete', 'trigger');


DROP TABLE ADSDOU
DROP TABLE ADComputer
DROP TABLE ADUser
go

set ARITHABORT ON
set QUOTED_IDENTIFIER OFF
go
-- DROP all Foreign keys, Primary keys, Unique constraints and indices:
-- from tables that are part of the ManageSoft core product
DECLARE cleanup CURSOR FOR
	SELECT '/*1'+c.xtype+'*'+'/ ALTER TABLE ['+t.name+'] DROP CONSTRAINT '+c.name AS cmd
	FROM	sysobjects AS t,
		sysobjects AS c,
		ManageSoftMigration as m
	WHERE	t.xtype = 'U'
	  AND	t.id = c.parent_obj
	  AND	(c.xtype = 'F' OR c.xtype = 'PK' OR c.xtype = 'UQ')
	  AND	t.name = m.name
	  AND	m.type = 'table'
	UNION
	SELECT	'/*3*/ DROP PROCEDURE '+p.name AS cmd
	FROM	sysobjects AS p,
		ManageSoftMigration as m
	WHERE	p.xtype = 'P'
	  AND	(p.name NOT LIKE 'sp_%' OR p.name NOT LIKE 'xp_%' OR p.name NOT LIKE 'dt_%')
	  AND	p.name = m.name
	  AND	m.type = 'procedure'
	UNION
	SELECT	'/*4*/ DROP TRIGGER '+r.name AS cmd
	FROM	sysobjects AS r,
		ManageSoftMigration as m
	WHERE	r.xtype = 'TR'
	  AND	r.name = m.name
	  AND	m.type = 'trigger'
	UNION
	SELECT	'/*5*/  EXEC sp_rename '''+t.name+''', ''Old'+t.name+'''' AS cmd
	FROM	sysobjects AS t,
		ManageSoftMigration as m
	WHERE	t.xtype = 'U'
	  AND	t.name NOT LIKE 'Old%'
	  AND	t.name NOT LIKE 'dt_%'
	  AND	t.name = m.name
	  AND	m.type = 'table'
	UNION
	SELECT	'/*6*/ DROP VIEW ['+v.name + ']' AS cmd
	FROM	sysobjects AS v,
		ManageSoftMigration as m
	WHERE	xtype = 'V'
	  AND	status >= 0
	  AND	v.name = m.name
	  AND	m.type = 'view'
	ORDER BY cmd

open cleanup
declare @clean AS varchar(256)
fetch NEXT FROM cleanup INTO @clean
while @@fetch_status = 0
begin
	print @clean
	exec(@clean)
	fetch NEXT FROM cleanup INTO @clean
end
close cleanup
deallocate cleanup

DECLARE cleanup CURSOR FOR
	SELECT	'/*2*/ DROP INDEX ['+t2.name + '].' + i.name AS cmd
	FROM	sysobjects AS t2, sysindexes AS i,
		ManageSoftMigration as m
	WHERE	t2.id = i.id
	  AND	t2.xtype = 'U'
	  AND	t2.name <> i.name
	  AND	i.indid <> 255
	  AND	(i.status & 0x800000) = 0
	  AND	t2.name = m.name
	  AND	m.type = 'table'

open cleanup
declare @clear AS varchar(256)
fetch NEXT FROM cleanup INTO @clear
while @@fetch_status = 0
begin
	print @clear
	exec(@clear)
	fetch NEXT FROM cleanup INTO @clear
end
close cleanup
deallocate cleanup

set ARITHABORT OFF
set QUOTED_IDENTIFIER ON
go
--
-- Adds new rows to SoftwarePublisher table
--
IF EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwarePublisher'
)
BEGIN
	PRINT 'Using existing SoftwarePublisher table'
	IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwarePublisher' AND COLUMN_NAME = 'SupportURL'
	)
	BEGIN
		PRINT 'Adding new columns to SoftwarePublisher table'
		ALTER TABLE SoftwarePublisher ADD SupportURL nvarchar(256) DEFAULT('') NOT NULL
		ALTER TABLE SoftwarePublisher ADD SupportPhone nvarchar(256) DEFAULT('') NOT NULL
		ALTER TABLE SoftwarePublisher ADD ContactName nvarchar(256) DEFAULT('') NOT NULL
		ALTER TABLE SoftwarePublisher ADD Comments nvarchar(512) DEFAULT('') NOT NULL
		PRINT 'Added new columns to SoftwarePublisher table'
	END
END
GO

--
-- Adds new rows to SoftwareReseller table
--
IF EXISTS (
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'SoftwareReseller'
)
BEGIN
	PRINT 'Using existing SoftwareReseller table'
	IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'SoftwareReseller' AND COLUMN_NAME = 'ContactName'
	)
	BEGIN
		PRINT 'Adding new columns to SoftwareReseller table'
		ALTER TABLE SoftwareReseller ADD ContactName nvarchar(256) DEFAULT('') NOT NULL
		ALTER TABLE SoftwareReseller ADD ContactPhone nvarchar(256) DEFAULT('') NOT NULL
		ALTER TABLE SoftwareReseller ADD Comments nvarchar(512) DEFAULT('') NOT NULL
		PRINT 'Added new columns to SoftwareReseller table'
	END
END
GO

