Monday, June 13, 2011

to find the SPs and delete

while migrating from ms crm 4.0 to 2011 , i was trying to import the DB i have taken after restoring.
The issues i have faced was the Cutom SPs and the Functions and Triggers are created by the 4.0 team.
1st need to clean them
to clean the SPs, we need to find them out, we can get some in the follwoing query unless untill the developers ahve given the proper names.
select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' -- this is CRM SP names
and name like '%' --SP name STD is the starting name your team has used

now copy and run the result from the above command.

to clean the Triggers we need to find them out, we can get them in the following query

SELECT S2.[name] TableName, 'drop trigger ' + S1.[name] TriggerName,
CASE WHEN S1.deltrig > 0 THEN 'Delete' WHEN S1.instrig > 0
THEN 'Insert' WHEN S1.updtrig > 0 THEN 'Update' END 'TriggerType'
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

now copy the whole 2nd column and run it from the result of the above query.


No comments:

Post a Comment