[SalesForce] Obtaining all ContentDocument records using SOQL

This is possible through Apex, but not SOQL --- and no Metadata API required.

To determine whether a given SObject's RecordType is available to a given user's profile, you will need to use the Apex DescribeSObjectResult getRecordTypeInfos() call on that SObject (see the docs here). This returns a list of RecordTypeInfo objects corresponding to all RecordTypes on that SObject, each of which has an isAvailable() method which will tell you if the RecordType is available to the running user's Profile.

For example, say that you have 2 Case Record Types, 'Phone Team' and 'Web Team', with both available to your Profile. Let's make 'Phone Team' unavailable to test. Go into your Profile, and scroll down to the Record Types area, and click on Case, and then remove 'Phone Team' from the list of available Record Types.

Now, add the following method to a utility class:

// Returns a List of the Names of all RecordTypes
// available to the running user for a given SOBject type
public static List<String> GetAvailableRecordTypeNamesForSObject(
    Schema.SObjectType objType
) {
    List<String> names = new List<String>();
    List<RecordTypeInfo> infos = objType.getDescribe().getRecordTypeInfos();
    // If there are 2 or more RecordTypes...
    if (infos.size() > 1) {
        for (RecordTypeInfo i : infos) {
           if (i.isAvailable() 
           // Ignore the Master Record Type, whose Id always ends with 'AAA'.
           // We check the Id because Name can change depending on the user's language.
            && !String.valueOf(i.getRecordTypeId()).endsWith('AAA'))
                names.add(i.getName());
        }
    } 
    // Otherwise there's just the Master record type,
    // so add it in, since it MUST always be available
    else names.add(infos[0].getName());
    return names;
}

And run the following from Anonymous Apex to get the available Case record types:

 List<String> availableCaseTypes = 
      GetAvailableRecordTypeNamesForSObject(Case.SObjectType);

 System.debug(availableCaseTypes);
 // returns ('Web Team')

Just FYI, the reason this is not possible from SOQL is that the only RecordType-related SObject exposed in SOQL is the RecordType object, whose IsActive field will tell you whether or not a RecordType is globally active/inactive, but provides no information whatsoever on whether a given RecordType is available to a user or profile.

I used the Salesforce UI to add a File to an Opportunity. Then checked the resulting ContentDocumentLink records with SQOL.

Select Id,LinkedEntityId,ContentDocumentId,IsDeleted,ShareType,Visibility from ContentDocumentLink where ContentDocumentId = '0694000000548HHAAY'

Note how there are two records, one to the Opportunity and one to my user record (this will be why you can't see it in your private library). The one to the Opportunity has the ShareType V (Viewer permission) and Visibility AllUsers