SELECT p.page_id,p.page_len,CONCAT("File:",p.page_title) FROM page p

LEFT JOIN page_restrictions ON pr_page = p.page_id 

LEFT JOIN categorylinks c2 ON c2.cl_from = p.page_id AND c2.cl_to IN (

  'DYK_images',

  'Wikipedia_image_placeholders',

  'Wikipedia_restricted_images',

  'Featured_pictures', 

  'Wikipedia_restricted_images',

  'Featured_pictures_on_Mathematics_Portal',

  'Featured_pictures_of_Star_Portal',

  'Wikipedia_Picture_of_the_day_files',

  'All_non-free_media',

  'Self-published_work',

  'Wikipedia_awards'

  'Copy_to_Wikimedia_Commons',

  'Copy_to_Wikimedia Commons_(inline-identified)',

  'All_free_in_US_media',

 ##'Pre-1996_PD_in_home_country_US_public_domain_images',

 'Unclaimed_images_thought_to_be_uploader',

  'Wikipedia_files_with_disputed_copyright_information',

 'File_where_self-authorship_has_been_assumed',

  'Files_where_a_release_under_a_free_license_has_been_assumed',

  'Files_deleted_on_Wikimedia_Commons'

  'Files_with_poor_sources',

  'Files_lacking_an_author',

  'Files_deleted_on_Wikimedia_Commons',

  'Wikipedia_files_on_Wikimedia_Commons_for_which_a_local_copy_has_been_requested_to_be_kept'

  'Unclaimed_images_thought_to_be_uploader'

)

LEFT JOIN templatelinks t ON t.tl_from = p.page_id

                          AND t.tl_namespace = 10

                          AND t.tl_title IN ('Non-free_use_rationale', 

                                             'Non-free_use_rationale_2', 

                                             'Non-free_media_rationale',

                                             'Non-free_media_data',

                                          ## Commons related 

                                             'Convert_to_SVG_and_copy_to_Wikimedia_Commons', 

                                             'Copy_to_Wikimedia_Commons', 

                                             'ShadowsCommons', 

                                             'Now_Commons', 

                                             'Do_not_move_to_Commons',

                                             'Keep_local',

                                             'Keep_local_high-risk',

                                             'No_Commons-requested',

                                             'No_Commons_requested',

                                            ## Missing license data

                                             'Somewebsite', 

                                             'No_copyright_information', 

                                             'No_copyright_holder',

                                             'Untagged',

                                             'untagged',

                                             'Wrong-license',

    ## local deltion process

                                             'Db-meta',

                                             'Db-redircommons',

                                             'Deletable_file',

                                             'Duplicate',

                                             'File_at_CCI',

                                             'Ffd',

                                             'hidden-delete-reason',

                                             'AutoReplaceable_fair_use_people',

                                             'Proposed_deletion/dated_files',

    ## Protected 

                                             'Restricted_use',

                                             'badimage',

                                             'Soft_redirect',

    ## featured content

                                             'FeaturedPicture',

                                             'Featured_picture', 

                                             'Former_featured_picture', 

                                             'Featured_sound',

                                             'Former_featured_sound', 

                                             'FSC', 

                                             'Gujarat_selected_picture', 

                                             'FPCold', 

                                             'VPCold', 

                                             'Thailandportal', 

                                             'Asian_Games_selected_picture',

                                             'picture_of_the_day', 

                                             'SP_Massachusetts',

                                             'SP_India_UP',

                                             'Nanotech_selected',

                                             'Portal:Star/Selected_picture/template',

                                             'Fishportal',

                                             'Brazilian_selected_picture',

                                             'USMCportal',

                                             'PISPC',  

    ## Other local tags for files at Commons (and eslewhere)

                                             'Qxz-adnavbox', 

                                             'DYKfile', 

                                             'Wikipedia-adnavbox',                                                                   

    ## OTRS

                                             'OTRS_pending', 

                                             'OTRS_received',

                                             'Img-unclaimed',

    ## Non-free media

                                             'NFUR_not_needed', 

                                             'Non-free_media',  

                                             'Out_of_copyright_in',

    ## Free in US media

                                             'Free_in_US_media',                                             

    ## "Problem" tags

                                             'Badimage',

                                             'Soft_redirect',                                       

    ## 'License tags needing manual review       

                                             'PD-Australia',

                                             'PD-AustraliaGov',

                                             'PD-Canada',

                                             'PD-Italy',

                                             'PD-US-1923-abroad',

                                             ## 'PD Gutenberg',

                                             'PD-Russia-2008',                                           

                                             'PD-textlogo',

                                             'PD-shape',

                                             'PD-Russia',                          

                                             'PD-because',

                                             ## 'PD-author',

                                             ## 'PD-US',

                                             ## 'PD-US-1923',

    ## Self##

                                           ##'PD-self',

                                           ##'GFDL-self', 

                                           ##'Self',

                                           ##'Multilicense_replacing_placeholder'

                                           ##'Multilicense_replacing_placeholder_new',

    ## Misc    

                                             'Userspace_file',

                                             'Esoteric_file', 

                                             'Example_files', 

                                            ## 'Information',

                                            ## 'Artwork',

                                            ## 'Spoken_article_entry',

                                            ## 'Google_Art_Project',                                              

                                             'M-cropped', 

                                             'Split_media',

                                             'Uploaded_from_Commons'

                                           ## 'Wikipedia-screenshot'

                                          )

​

WHERE t.tl_from IS NULL

AND c2.cl_from IS NULL

AND pr_page IS NULL

AND p.page_is_redirect = FALSE

AND p.page_len > 0

AND p.page_namespace = 6