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

Get Picklist Value By Picklist Name

I wrote some method to get picklist value by knowing picklist name.

namespace test
{
    public class CrmServiceProvider
    {

  private WebMetadataService.MetadataService _MetaService = null;
  
  ///* Create a MetadataService end point */
        private WebMetadataService.MetadataService MetaService
        {
            get
            {
                if (_MetaService == null)
                {
                    _MetaService = new WebMetadataService.MetadataService();
                    _MetaService.Url = "http://localhost/mscrmservices/2007/metadataservice.asmx";

                    // Use a special user credentials
     NetworkCredential cred = new NetworkCredential();
                    cred.Domain = ConfigurationManager.AppSettings["Domain"];
                    cred.UserName = ConfigurationManager.AppSettings["UserName"];
                    cred.Password = ConfigurationManager.AppSettings["PassWord"];

                    _MetaService.UseDefaultCredentials = false;
                    _MetaService.Credentials = cred;

                    _MetaService.UnsafeAuthenticatedConnectionSharing = true;

                    WebMetadataService.CrmAuthenticationToken token = new WebMetadataService.CrmAuthenticationToken();
                    token.AuthenticationType = 0;
                    token.OrganizationName = ConfigurationManager.AppSettings["Organization"];

                    _MetaService.CrmAuthenticationTokenValue = token;
                }
                return _MetaService;
            }
        }


  public int GetPicklistValueByPicklistName(string PicklistName, string EntityLogicalName, string LogicalName)
  {
   int retval = -1;
   
   /* Retrieve the attribute metadata */
   WebMetadataService.RetrieveAttributeRequest attributeRequest = new WebMetadataService.RetrieveAttributeRequest();
   attributeRequest.EntityLogicalName = EntityLogicalName;
   attributeRequest.LogicalName = LogicalName; //picklist

   WebMetadataService.RetrieveAttributeResponse attributeResponse =
      (WebMetadataService.RetrieveAttributeResponse)MetaService.Execute(attributeRequest);

   /* Cast the attribute metadata to a picklist metadata */
   WebMetadataService.PicklistAttributeMetadata picklist =
      (WebMetadataService.PicklistAttributeMetadata)attributeResponse.AttributeMetadata;


   foreach (WebMetadataService.Option op in picklist.Options)
   {
    if (op.Label.UserLocLabel.Label == PicklistName)
     retval = op.Value.Value;
   }

   return retval;
  }
 }
}

To call this method use the next construction:

int psv = GetPicklistValueByPaymentSystemName(categorycode, "account", "accountcategorycode");
if (psv > 0)
{
 acc.accountcategorycode = new Picklist();
 acc.accountcategorycode.Value = psv;
}