All of the information you seek is in the system objects that Gail Shaw noted in her post above. If you need the information you state, then you should understand that information, as well. The only way you'll understand all of that information is to read about each of those objects in Books Online and figure this one out on your own. You won't be sorry that you did. Think of it as a "rite of passage" that every DBA should go through. 😉 If you have problems during your attempt, I'm sure that folks would be happy to help you with specific prroblems instead of writing the entire query for you.