A detective (aka investigator) was an agent who investigated crimes in order to solve them. This could be done on behalf of a government or as one's private occupation. Someone who did it privately was known as a criminal investigator, private investigator, private eye, or P.I.
In the Earth movie Appointment with Danger, seen in T'Pol's nightmare in 2153, a character was referred to by Doctor Phlox as "the detective in the bow tie." He believed that the man hadn't really died in a house fire and would be seen later. (ENT: "Impulse")
Dixon Hill was a fictional 20th century Earth detective. Captain Jean-Luc Picard was fond of Dixon Hill novels, and in his free time would often escape to the holodeck where he would assume the role of Hill in holographic recreations of the detective's stories. (TNG: "The Big Goodbye", "Manhunt", "Clues"; Star Trek: First Contact)
Miles O'Brien was fond of the Mike Hammer detective novels by Mickey Spillane, including I, the Jury and Kiss Me Deadly. (DS9: "Profit and Loss", "Shadows and Symbols") Despite this, O'Brien himself was an engineer, not a detective, as Captain Benjamin Sisko pointed out when O'Brien went missing after searching for the widow of Liam Bilby in New Sydney. (DS9: "Prodigal Daughter")