Geek Answers Handbook

Most useful SQL meta queries

I have a passion for meta queries, which I mean queries that answer questions about data, not answers to data.

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

My motivation for using meta queries is:

  • When I sit down with a new database and want to understand it, I examine meta-queries. The most common ones are those that allow me to answer questions about fields and tables, for example, "What other tables have this" xyz "field?" or "What tables have identifier columns?" or "What are the keys for this table?"
  • I regularly work with several types of databases (SQL Server, Oracle, MySql) and - I practice a great ideal for programming laziness - I do not want to search or remember a secret SQL recipe every time I need it, I want to point and click.

Are other (better?) Ways to get meta-information sure for a particular type of database. SQL Server, in particular, provides SQL Server Management Studio. The Oracle and MySql tools do not seem to provide the same benefit. (I completely agree that I am making this expression with my SQL-Server-oriented view of the universe. :-) Even if they did, they would be different - I want a uniform approach to database types.

So finally the question:

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

Pivot Matrix

This first view summarizes my collection so far by the type of database (and, as I said, is heavily weighted in 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

Database Type Links

I myself developed some of these meta queries, but many of them appeared on community forums. This second view lists the source URLs where necessary.

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
+6
sql oracle mysql sql-server
source share
6 answers

Oracle SQL Developer has a set of built-in reports that include these categories. I 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 actual report names,

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

Many more reports are available.

+1
source share

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

  • Tables without primary keys
  • Tables without a clustered index
  • Tables without indexes
  • Scalar user functions that are not deterministic
  • A database object that does not have the extended MS_Description property (the default Description property, which is useful for creating documentation)
  • Schemes that are empty
  • SQL modules (views, procs, functions, triggers) without standard documentation / comment blocks

The system is specific:

  • Configuration tables containing links to missing stored procedures or views
  • Views based on tables / views that cannot be encoded or verified (since they are based on a view / table in another database)
  • Columns in views that are not used in the system
  • Certain NULLable column types that do not have default values
  • Numeric columns that are NULLable
+1
source share

In Oracle, the most useful is the one that is on v$session about expectations on running sessions, this means what the session is doing at that moment (reading from disk, waiting for a lock, ...)

0
source share

Oracle has a wide range of metadata representations, probably the one I request the most will be DBA_OBJECTS, which can be requested for all types of different types of objects. Such information and much more can be obtained from other views (for example, more information about tables can be found in DBA_TABLES).

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

0
source share

The problem with using a set of canned scripts from the Internet is that "this is not what you know, this is what you know, it is not, or it is not." You need to make sure that when you raise the scripts that it matches the version. For example, Oracle as of 10.1 or 10.2 allows you to set the UNUSED column. It is still displayed in DBA_TAB_COLUMNS, but it no longer works.

It is better to understand that in the Data Dictionary - in particular, in Oracle - the contents of the database directory (V $, DBA_ *) and links to PL / SQL packages and types, as more and more functions move in this direction (for example, a package DBMS_STATS with ANALYZE)

Some of the more esoteric but useful in Oracle:

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

DBA_TAB_MODIFICATIONS -- in which tables the number of attachments / update / removal of traffic since the last analysis was indicated.

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

V$SESSION_LONGOPS -- Which SQL statements work with "long" operations, such as full validation, sorting and merging, and for how long Oracle thinks it will be before it ends.

DBA_HISTOGRAMS -- What existed in your data was

DBA_OBJECTS -- he got everything

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

0
source share

msorens,

I totally agree with you. Understanding data and schema helps to code better, avoid errors, identify special cases and analyze requirements.

You might want to check out my other post at:
Compare the two schemas and update the old schema with the new columns of the new schema

Comparing my script schemas is information about the views of the Oracle directory. Unfortunately, it is not completed, but this is the task the next day .; -)

Matthew

0
source share

More articles:


All Articles