Often times we run into situations where we need to pull information from Sitecore.  Two of my favorite tools to do this are Sitecore Rocks and Sitecore PowerShell Extensions (SPE).

For those of you who are unfamiliar with these tools, Sitecore Rocks is a Visual Studio extension that makes developing and interacting with Sitecore easy.  SPE is an installed module into Sitecore that gives you a scripting environment and a command line interface.  We will simply be executing queries, but I highly recommend reading their respective documentation sites to learn about their full set of features.

I don’t claim to be a PowerShell guru, so what I’ve done is create simple queries you can run in both Sitecore Rocks and SPE.  You can write pure PowerShell queries, but I find the ones I’ve created to feel overly complex.  So for any query below, you can execute this in SPE with the following:

Get-Item -Path master: -Query "/sitecore/templates/Feature//*[@@templatename = 'Template']"
Or like this so you can easily define the columns into a table:
$items = Get-Item -Path master: -Query "/sitecore/templates/Feature//*[@@templatename = 'Template']"

$items | Format-Table Name, @{ Label = 'Path'; Expression={ $_.Paths.Path } },  @{ Label = 'Std Val'; Expression={ $_["__Standard values"] } }

You can get more information about Working with Items and getting item by XPath on the SPE doc site.  If you have built indexes and aren’t working off of restored databases, a performant way to do this would be finding items using the content search API.
The examples provided below are intended to be run in the Sitecore Rocks Query Analyzer.  I highly recommend reading 28 Days of Sitecore Rocks: Query Analyzer to get started.
Standard Values of Templates

Are there Standard Values on all the templates?
select @@Name as Name, @@Path as Path, @#__Standard values# as #Standard Values# from /sitecore/templates/Feature//*[@@templatename = 'Template']
Types of Fields

Are there fields of type Multilist and what is their source?
select @@Name as Name, @Type, @Source, @@Path as Path from /sitecore/templates/Feature//*[@Type = 'Multilist']
Are there fields of type Treelist and what is their source?
select @@Name as Name, @Type, @Source, @@Path as Path from /sitecore/templates/Feature//*[@Type = 'Treelist']
What are all the fields, their types, and their sources regardless of their template? Order them by Type and Name.
select @@Name as Name, @Type, @@Path as Path from /sitecore/templates/Project//*[@@templatename = 'Template field'] order by Type, Name
List of Templates

Just give me a list of all the templates.
select @@Name as Name, @@Path as Path from /sitecore/templates/Project//*[@@templatename = 'Template']
List of Content Items

Where are all my content items based on the Article template in the Draft workflow state?  The GUID in this example is of the Draft item in a the Sample Workflow.
select @@Name as Name, @@Path as Path from /sitecore/content//*[@@templatename = 'Article' and contains(@#__Workflow state#, "{190B1C84-F1BE-47ED-AA41-F42193D9C8FC}")] order by Path
Rich Text Editor fields with HTML editor profiles defined

What are all the rich-text fields and their sources? (originally from Sitecore John himself)
select @@path, @Source from /sitecore/templates//*[@@templatekey = 'template field' and @type = 'Rich Text'] order by Source
Search for Tokens in Fields

Where are all the single-line text fields with a $name token?
select @@Name as Name, @Type, @@Path as Path from /sitecore/templates//*[@type = 'Single-Line Text' and contains(@title, '$name')]
Explorer Sitecore Rocks Tools

Right next to the Execute Command in the ribbon, there are some really useful tools!
Insert Fields gives you a list of every field available out of Sitecore.  As it is connected to your Sitecore instance, this means it will pull the fields off of the items specific to your environment!

Exporting Data with Sitecore Rocks
One of my favorite features is the ability easily export the results.  Simply right click on the Results tab.  This is invaluable for providing an ad hoc report of items.  For example, we could export the results of the “Article” query we performed.

For repeatable queries/reports you want Sitecore users to access, consider a different approach with Sitecore PowerShell Extensions Dynamic Reports.
The Big Gotcha
When using a Sitecore Query, it’s important to be cognizant of the configured Query.MaxItems value in the Sitecore configs.  Depending on your version of Sitecore, it may be set to 100 or 260 by default.  I typically set this to 9999 on my local, but I wouldn’t do this on a production environment.
Disclaimer
These queries are intended for developers to gather information.  They are not optimized for performance and should not be used in a production environment.