ansaurus

Question

Most useful SQL meta-queries

Answer 1

+1 A:

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

Cade Roux 2010-02-23 21:27:39

Answer 2

A:

On Oracle the most useful is the one on v$session about the waits on the running sessions, it means, what are the session doing in that moment (reading from disc, waiting for lock, ...)

FerranB 2010-02-24 00:03:36

Answer 3

A:

Oracle has a large range of metadata views, probably the one I query the most would be DBA_OBJECTS, which can be queried for all kinds of different object types. The same info and more can be obtained from other views (e.g. more info about the tables may be found in DBA_TABLES).

A good overview of the Oracle data dictionary may be found here.

Jeffrey Kemp 2010-02-24 07:33:35

Answer 4

A:

The concern with using a set of canned scripts off the internet is that "it's not what you know, it's what you know that isn't so, or isn't so anymore." One needs to make sure when lifting scripts it's version appropriate. For example, Oracle as of 10.1 or 10.2 enables setting a column as UNUSED. It still shows up in DBA_TAB_COLUMNS, but it's not really there anymore.

It's better to understand what's in the Data Dictionary -- specifically in Oracle, the contents of the Database Reference (V$, DBA_*) and the PL/SQL Packages and Types reference, as more and more functionality moves in that direction (e.g. DBMS_STATS package superceding ANALYZE statement)

Some of the more esoteric but useful ones in Oracle:

DICT -- a name and a brief description of every table\view in the data dictionary.

DBA_TAB_MODIFICATIONS -- which tables have had how much insert/update/delete traffic since last analyzed.

V$OBJECT_USAGE -- when used with ALTER INDEX ... MONITORING USAGE, shows which indexes have not been used in SQL statements since monitoring was enabled. (Indexes used to support foreign key or unique constraints may not appear, but may have been "used" nonetheless.)

V$SESSION_LONGOPS -- what SQL statements are running "long running" operations, like full scans, sorts, and merges, and how long does Oracle think it'll be before it finishes.

DBA_HISTOGRAMS -- What skew has existed in your data

DBA_OBJECTS -- it's got everything

DBA_SOURCE (by line)/ DBA_TRIGGERS (by block)-- all the executable code in the system.

Adam Musch 2010-02-24 15:13:12

Quite correct on versioning--in my QueryPicker library I do not have any sufficiently fancy Oracle queries that warrant it (that I know of:-) but for SQL Server I do, so I simply decorate the query name as in, for example, "database properties--SS2005 or later".

msorens 2010-02-24 18:42:23

Answer 5

+1 A:

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.

Janek Bogucki 2010-02-24 15:43:11

Excellent tip! This important feature does not really jump out at you looking at SqlDeveloper's menus. So are those reports generated via PL/SQL and if so, how does one view the source?

msorens 2010-02-24 18:47:30

This applies to SQL Developer 1.5. I expect it is the same in SQL Developer 2.1 but I have not checked.After running the report click on the SQL button titled "Run report in SQLWorksheet". This copies the sql into a worksheet where it can be viewed, edited and executed.

Janek Bogucki 2010-02-24 20:58:26

Perfect--that gives me a rich source of queries to integrate into QueryPicker. Just curious if you know (and I am also running SqlDeveloper 1.5), when you double-click a report it asks for "bind values" (i.e. query parameters)--but it does not let you specify owner. In my environment that is a necessity so I cannot run most of the queries directly (I have to open and edit the query per your tip to change the owner). Is there any way to get it to prompt for owner?

msorens 2010-02-25 00:13:16

Answer 6

A:

Matthew Eyles 2010-02-25 14:10:49

ansaurus

tags:

views:

answers:

Most useful SQL meta-queries

So, finally, the question:

Summary Matrix

References By Database Type

SQL Server

Oracle

MySql

related questions