Most useful SQL meta-queries

Question

I have a passion for meta-queries, by which I mean queries that answer questions about data rather than answering with data.

Before I get a lot of justified criticism, I do realize that the approach of meta-queries is not ideal, as eloquently described here for example. Nevertheless, I believe they do have their place. (So much so that I created a WinForms user control that supports parameterized meta-queries for SQL Server, Oracle, and MySql, and I describe extensively the design and use of this QueryPicker in a three-part series published on Simple-Talk.com.)

My motivation for using meta-queries:

1. When I sit down with a new database and want to understand it, I probe with meta-queries. Most common are those that let me answer questions about fields and tables, such as "What other tables have this 'xyz' field?" or "What tables have identity columns?" or "What are the keys for this table?"
2. I regularly work with multiple database types (SQL Server, Oracle, MySql) and--practicing the great programming ideal of laziness--I do not want to have to look up or remember an arcane SQL recipe every time I need it. I want to point and click.

Sure there are other (better?) ways to get meta-information--for a given database type. SQL Server, particularly, provides SQL Server Management Studio. Oracle and MySql tools do not seem to provide the same usefulness. (I freely admit that I make this claim with my SQL-Server-leaning-view of the universe. :-) Even if they did, they would be be different--I want a uniform approach across database types.

---

So, finally, the question:

What SQL Server, Oracle, or MySql meta-queries do you find useful?

---

Summary Matrix

This first view summarizes my collection thus far by database type (and, as I said, heavily weighted toward SQL Server).

Query                            SQL Server    Oracle     MySql

DB Version                           yes        yes        yes
Databases with properties            yes                   yes
Databases with space usage           yes
National Language Support                       yes
Procedures and functions             yes                   yes
Primary keys                         yes                   yes
Primary to foreign keys              yes
Session Information/brief            yes
Session Information/details          yes
Session SET options                  yes
Users and Roles                      yes
Currently running statements         yes
Constraints                          yes
Indexes                              yes
Column info/brief                    yes        yes        yes
Column info/details                  yes        yes        yes
Object level details                 yes
Rows and space used                  yes
Row/column counts                    yes
Non-empty tables                     yes        yes        yes
Show table schema                    yes        yes
Seed/max values                      yes

---

References By Database Type

I have developed some of these meta-queries myself but many have come from community forums. This second view itemizes the source URLs where appropriate.

SQL Server

System Category
-----------------
    DB Version
    Databases with properties    http://www.mssqltips.com/tip.asp?tip=1033
    Databases with space usage   http://www.sqlservercentral.com/Forums/Topic261080-5-1.aspx
    Procedures and functions
    Primary keys                 http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-all-the-primary-keys-in-a-database.html
    Primary to foreign keys      http://www.sqlservercentral.com/scripts/Miscellaneous/61481/
    Session Information/brief    http://www.sqlservercentral.com/blogs/glennberry/archive/2009/12/28/how-to-get-a-count-of-sql-connections-by-ip-address.aspx
    Session Information/details  http://www.mssqltips.com/tip.asp?tip=1817
    Session SET options
    Users and Roles              http://www.sqlservercentral.com/scripts/users/69379/
    Currently running statements http://www.sqlservercentral.com/articles/DMV/64425/
    Constraints
    Indexes                      http://www.sqlservercentral.com/scripts/Index+Management/63932/

Column Category
-----------------
    Column info/brief
    Column info/details

Table Category
-----------------
    Object level details
    Rows and space used          http://www.mssqltips.com/tip.asp?tip=1177
    Row/column counts
    Non-empty tables

DDL Category
-----------------
    Show table schema            http://www.sqlservercentral.com/scripts/Create+DDL+sql+statements/65863/

Data Category
-----------------
    Seed/max values

Oracle

System Category
-----------------
    DB Version
    National Language Support

Column Category
-----------------
    Column info/brief
    Column info/details

Table Category
-----------------
    Non-empty tables

DDL Category
-----------------
    Show table schema

MySql

System Category
-----------------
    DB Version
    Databases
    Procedures and functions
    Primary keys                 http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-all-the-primary-keys-in-a-database.html

Column Category
-----------------
    Column info/brief
    Column info/details

DDL Category
-----------------
    Show table schema

Answer 1

Oracle SQL Developer has a set of built-in reports that include these categories. I have expanded one of the categories.

About Your Database
All Objects
Application Express
ASH and AWR
Database Administration
  All Tables
  Cursors
  Database Parameters
  Locks
  Memory
  Sessions
  Storage
  Top SQL
  Users
  Waits and Events
Data Dictionary
Jobs
PLSQL
Security
Streams
Table
XML

These are a few of the actual report names,

Tables without Indexes
Tables without Primary Keys
Tables with Unindexed Foreign Keys
Largest Average Row Length
Most Rows
Unusable Indexes

There are many more reports available.

Answer 2

I have a number of these I use regularly on SQL Server, including, but not limited to:

• Tables without primary keys
• Tables without a clustered index
• Tables without any indexes
• Scalar User-Defined Functions which are not deterministic
• Database object which do not have extended property 'MS_Description' (the default 'Description' property, which is useful for generating documentation)
• Schemas which are empty
• SQL modules (views, procs, functions, triggers) without standard documentation/comment blocks

System-specific:

• Configuration tables which contain references to missing stored procedures or views
• Views based on tables/views which cannot be schemabound or verified (because they are based on a view/table in another database)
• Columns in views which are unused in the system
• Certain kinds of NULLable columns which do not have defaults
• Numeric columns which are NULLable