Get the localized names of all entities

select  ObjectTypeCode, Name, LogicalName, l.Label
from MetadataSchema.Entity e left join
MetadataSchema.LocalizedLabel l on e.EntityId=l.ObjectId
where l.ObjectColumnName='LocalizedName'
 and l.CustomizationLevel=1

union

select  ObjectTypeCode, Name, LogicalName, l.Label
from MetadataSchema.Entity e left join
MetadataSchema.LocalizedLabel l on e.EntityId=l.ObjectId
where l.ObjectColumnName='LocalizedName'
 and l.CustomizationLevel=0 
 and ObjectTypeCode not in 
  ( select  ObjectTypeCode
   from MetadataSchema.Entity e left join
   MetadataSchema.LocalizedLabel l on e.EntityId=l.ObjectId
   where l.ObjectColumnName='LocalizedName'
    and l.CustomizationLevel=1
  )

Get the params of all localized attributes and all picklist values

SELECT
 e.Name as EntityName
 ,l.Label as 'Наименование атрибута', a.LogicalName ,at.Description as 'Тип атрибута' ,a.AttributeLogicalTypeId
 ,apv.Value as 'Значения пиклиста', lp.Label as 'Наименования пиклиста'
 ,[AttributeRequiredLevelId]
 ,[MaxLength]
 ,[MinValue]
 ,[MaxValue]
 ,LookupClass
 ,LookupStyle
 ,LookupBrowse
FROM
 [NaviconGroup_MSCRM].[MetadataSchema].[Attribute] a
 join MetadataSchema.Entity e on e.EntityId=a.EntityId
 join MetadataSchema.AttributeTypes at on at.AttributeTypeId=a.AttributeTypeId
 join MetadataSchema.LocalizedLabel l on l.ObjectId=a.AttributeId and l.ObjectColumnName='DisplayName'
 left join MetadataSchema.AttributePicklistValue apv on apv.AttributeId=a.AttributeId
 left join MetadataSchema.LocalizedLabel lp on lp.ObjectId=apv.AttributePicklistValueId
order by EntityName, l.Label, at.Description,apv.Value

Registering plugins for event on many-to-many entities

I found that from the box MS CRM 4.0 are not supported the events on many-to-many relationship entities(bridge entities). But Aaron Elder has hack it.

All you need is just register it at AssociateEntities or DiassociateEntities event with empty Primary and Secondary Entity fields.

-- ============================================================================
-- Enable Associate and Disassociate Plug-in Events Script v1.0
-- ----------------------------------------------------------------------------
-- (c) 2009 Aaron Elder
-- ============================================================================
-- DISCLAIMER:
-- This script is provided "AS IS" with no warranties, and confers no rights.
-- ============================================================================
-- While this is obviously "unsupported", I think the fact that these events
-- are not available is a bug and hopefully it will be fixed in a rollup.
-- ============================================================================

USE AscentiumCrmDev_MSCRM
GO

-- Find the deployments SDK Filter ID for the
-- Associate and Disassociate Entity SDK Messages
DECLARE @DisassociateEntitiesFilterId uniqueidentifier
DECLARE @AssociateEntitiesFilterId uniqueidentifier
SET @DisassociateEntitiesFilterId = (SELECT SdkMessageId FROM SdkMessageBase WHERE [Name] = 'DisassociateEntities')
SET @AssociateEntitiesFilterId = (SELECT SdkMessageId FROM SdkMessageBase WHERE [Name] = 'AssociateEntities')

-- Enable the Associate and Disassociate Filters to be valid for custom processing
-- Custom Processing means "you register plug-ins against it"
-- Note: We only do this for the "generic" (OTC == 0) case, just to be safer
UPDATE SdkMessageFilterBase SET IsCustomProcessingStepAllowed = 1
       WHERE SdkMessageId = @DisassociateEntitiesFilterId AND PrimaryObjectTypeCode = 0


UPDATE SdkMessageFilterBase SET IsCustomProcessingStepAllowed = 1
       WHERE SdkMessageId = @AssociateEntitiesFilterId AND PrimaryObjectTypeCode = 0

(C)
http://consulting.ascentium.com/blog/crm/Post533.aspx

Get contact status in custom extended marketing list

declare @contactid uniqueidentifier
declare @campaignactivityid uniqueidentifier
-- some Guids
set @contactid='48CF39EE-AA8C-DE11-A526-000C2989FA53'
set @campaignactivityid='72CE8156-AD8C-DE11-A526-000C2989FA53'

select exL.New_ExtMarketingListsElementId, exL.New_contactStatus
from dbo.New_ExtMarketingListsElementExtensionBase exL
 JOIN dbo.ContactBase cnt ON exL.new_contact=cnt.ContactId
 JOIN dbo.ListBase lst ON exL.new_list=lst.ListId
 JOIN dbo.CampaignActivityItemBase cai ON lst.ListId=cai.ItemId
where cnt.ContactId=@contactid 
 and cai.CampaignActivityId=@campaignactivityid

How to get Marketing Lists by Campaign Activity Id

declare @campaignactivityid uniqueidentifier
-- an example GUID
set @campaignactivityid='72CE8156-AD8C-DE11-A526-000C2989FA53'

-- связь между маркетинговым списком и кампанией осуществляется через промежуточную таблицу CampaignActivityItemBase
select * 
from dbo.CampaignActivityItemBase cai
 JOIN dbo.ListBase lb ON lb.ListId=cai.ItemId
where cai.CampaignActivityId=@campaignactivityid

How to get a PhoneCallId

SELECT
  ActivityPartyBase.PartyId AS ContactId,
  ActivityPartyBase.ActivityId AS PhoneCallId
FROM

CampaignActivityBase
INNER JOIN ActivityPointerBase

ON (ActivityPointerBase.RegardingObjectId = CampaignActivityBase.ActivityId)AND (ActivityPointerBase.DeletionStateCode = 0) 

INNER JOIN PhoneCall 

ON ((PhoneCall.ActivityId = ActivityPointerBase.ActivityId) AND (ActivityPointerBase.StateCode = 0) AND (PhoneCall.DeletionStateCode = 0))

INNER JOIN ActivityPartyBase

ON ((PhoneCall.ActivityId = ActivityPartyBase.ActivityId) AND (PartyObjectTypeCode = 2))

INNER JOIN ContactBase

ON (ContactBase.ContactId = ActivityPartyBase.PartyId) AND (ContactBase.DeletionStateCode = 0)


WHERE 
  (CampaignActivityBase.ActivityId = @CampaignActivityId)

MSCRM, SQL and Localization

If you need to look at localized name of some attribute thru the SQL then check out next code

select l.* 
from MetadataSchema.LocalizedLabel l
 join MetadataSchema.Attribute a on l.objectid=a.AttributeId
 join MetadataSchema.Entity e on a.EntityId=e.EntityId  
where a.name= 'address1_city' and e.name='account'

MS CRM database fragmentation

If your CRM is very slow, then check the indexes fragmentation.
And if received table was not empty then you must rebuild the indexes.

USE AdventureBase
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureBase'), NULL,NULL, NULL, NULL) AS a
 JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id    
where avg_fragmentation_in_percent>30
GO