1) Explicit expressions for transition densities
In the case of linear systems with additive noise of the form
$d X(t) = (A X_t + b) \, d t + \sigma \, d B_t$
it is possible to obtain an explicit solution, see Ioannis Karatzas and Steven E. Shreve, MR 1241411 Brownian motion and stochastic calculus, Section 5.6.
There are a few other explicitly solvable cases (in terms of transition density), for example the SDE for the Bessel process
$d X_t = (d-1)/(2X_t) \, d t + B_t$,
and the exponential Brownian motion,
$d X_t = a X_t \, d t + \sigma X_t \, d B_t$,
and quite a few other examples. In general finding the transition density of an SDEs means that you have solved the associated Fokker-Planck equation (equivalently known as forward Kolmogorov equation) which is a parabolic PDE. So finding transition densities is in general of the same difficulty as solving a linear PDE of a particular type, and solving such PDEs explicitly is typically not possible.
Also noteworthy is the Lamperti transform to change from general one-dimensional diffusion coefficients to a diffusion $Z$ with unit diffusion coefficient, by defining $Z_t = \varphi(X_t)$ where $\varphi(x) = \int^x \frac{1}{\sigma(\xi)} \ d \xi$.
I would recommend to consult a basic textbook focussing on explicit computations such as Øksendal's and look at the exercises to get a feel for what is possible in this direction.
2) Regularity of solutions
Of course, if $\sigma = 0$, your solution will be a deterministic trajectory, with corresponding Dirac measure as transition kernel, i.e. not belonging to any $L^p$. A simple and well known result is that if $\sigma(x)$ is globally bounded away from 0, you will have smooth transition densities. The important generalization of this result for multi-dimensional SDEs where some of the components of $\sigma$ may be zero is given by Hörmander's theorem. I recommend Martin Hairer's lecture notes, Convergence of Markov processes, for a gentle introduction. I am not sure about conditions for densities to belong to $L^p(\mathbb R)$.