SQLUtilities : Various SQL utilities - Formatting, generate - columns lists, procedures
| script karma |
Rating 1/1,
Downloaded by 112 |
| created by |
| David Fishburn |
| |
| script type |
| utility |
| |
| description |
Various SQL Utilities.
1. A SQL formatter, to make SQL statements (select, insert, update, delete...) more readable.
2. Based on create table statements, will generate select lists for a table, as long as the definition exists in some open buffer.
3. Creates a generic procedure that will perform an insert, update, delete and select based on the definition of a table (if already open in some buffer). The format is ANSI.
4. Returns the column datatype definition for a specified column name (or supplied) based on the definition of a table (if already open in some buffer).
" Functions:
" [range]SQLFormatter(..list..)
"
" Formats SQL statements into a easily readable form.
" Breaks keywords onto new lines.
" Forces column lists to be split over as many lines as
" necessary to fit the current textwidth of the buffer,
" so that lines do not wrap.
" If parantheses are unbalanced (ie a subselect) it will
" indent everything within the unbalanced paranthesis.
" Works for SELECT, INSERT, UPDATE, DELETE statements.
"
" Examples:
"
" Original:
" SELECT m.MSG_ID, m.PRIORITY_ID FROM MESSAGES m JOIN PRIORITY_CD P
" WHERE m.to_person_id = ? AND p.NAME = 'PRI_EMERGENCY'
" AND p.JOB = 'Plumber' AND m.status_id < (
" SELECT s.STATUS_ID FROM MSG_STATUS_CD s
" WHERE s.NAME = 'MSG_READ' ) ORDER BY m.msg_id desc
"
" Formatted:
" SELECT m.MSG_ID, m.PRIORITY_ID
" FROM MESSAGES m
" JOIN PRIORITY_CD P
" WHERE m.to_person_id = ?
" AND p.NAME = 'PRI_EMERGENCY'
" AND p.JOB = 'Plumber'
" AND m.status_id < (
" SELECT s.STATUS_ID
" FROM MSG_STATUS_CD s
" WHERE s.NAME = 'MSG_READ' )
" ORDER BY m.msg_id desc
"
" Original:
" UPDATE "SERVICE_REQUEST" SET "BUILDING_ID" = ?, "UNIT_ID" = ?,
" "REASON_ID" = ?, "PERSON_ID" = ?, "PRIORITY_ID" = ?, "STATUS_ID" = ?,
" "CREATED" = ?, "REQUESTED" = ?, "ARRIVED" = ? WHERE "REQUEST_ID" = ?
"
" Formatted:
" UPDATE "SERVICE_REQUEST"
" SET "BUILDING_ID" = ?,
" "UNIT_ID" = ?,
" "REASON_ID" = ?,
" "PERSON_ID" = ?,
" "PRIORITY_ID" = ?,
" "STATUS_ID" = ?,
" "CREATED" = ?,
" "REQUESTED" = ?,
" "ARRIVED" = ?,
" WHERE "REQUEST_ID" = ?
"
" Original:
" INSERT INTO "MESSAGES" ( "MSG_ID", "TO_PERSON_ID",
" "FROM_PERSON_ID", "REQUEST_ID", "CREATED", "PRIORITY_ID",
" "MSG_TYPE_ID", "STATUS_ID", "READ_WHEN", "TIMEOUT",
" "MSG_TXT", "RESEND_COUNT" ) VALUES ( ?, ?, ?,
" ?, ?, ?, ?, ?, ?, ?, ?, ? )
"
" Formatted:
" INSERT
" INTO "MESSAGES" ( "MSG_ID", "TO_PERSON_ID",
" "FROM_PERSON_ID", "REQUEST_ID", "CREATED",
" "PRIORITY_ID", "MSG_TYPE_ID", "STATUS_ID",
" "READ_WHEN", "TIMEOUT", "MSG_TXT", "RESEND_COUNT" )
" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
"
"
" Functions:
" CreateColumnList( optional parameter )
"
" Assumes either the current file, or any other open buffer,
" has a CREATE TABLE statement in a format similar to this:
" CREATE TABLE customer (
" id INT DEFAULT AUTOINCREMENT,
" last_modified TIMESTAMP NULL,
" first_name VARCHAR(30) NOT NULL,
" last_name VARCHAR(60) NOT NULL,
" balance NUMERIC(10,2),
" PRIMARY KEY( id )
" );
" If you place the cursor on the word customer, then the
" unnamed buffer (also displayed by an echo statement) will
" contain:
" id, last_modified, first_name, last_name, balance
"
" Optionally, it will replace the word with the above and place
" the word in the unnamed buffer. Calling the function with
" a parameter enables this feature.
"
"
" Functions:
" GetColumnDef( optional parameter )
"
" Assumes either the current file, or any other open buffer,
" has a CREATE TABLE statement in a format similar to this:
" CREATE TABLE customer (
" id INT DEFAULT AUTOINCREMENT,
" last_modified TIMESTAMP NULL,
" first_name VARCHAR(30) NOT NULL,
" last_name VARCHAR(60) NOT NULL,
" balance NUMERIC(10,2),
" PRIMARY KEY( id )
" );
" If you place the cursor on the word first_name, then the
" column definition will be placed in the unnamed buffer (and also
" displayed by an echo statement).
" VARCHAR(30) NOT NULL
"
" Optionally, it will replace the word with the above and place
" the word in the unnamed buffer. Calling the function with
" a parameter enables this feature.
"
"
" Functions:
" CreateProcedure()
"
" Assumes either the current file, or any other open buffer,
" has a CREATE TABLE statement in a format similar to this:
" CREATE TABLE customer (
" id INT DEFAULT AUTOINCREMENT,
" last_modified TIMESTAMP NULL,
" first_name VARCHAR(30) NOT NULL,
" last_name VARCHAR(60) NOT NULL,
" balance NUMERIC(10,2),
" PRIMARY KEY( id )
" );
" By calling CreateProcedure while on the name of a table
" the unnamed buffer will contain the create procedure statement
" for insert, update, delete and select statements.
" Once pasted into the buffer, unneeded functionality can be
" removed.
"
"
"
" Commands:
" [range]SQLFormatter ..list..
" : Reformats the SQL statements over the specified
" range. Statement will lined up given the
" existing indent of the first word.
" CreateColumnList : Creates a comma separated list of column names
" for the table name under the cursor, assuming
" the table definition exists in any open
" buffer. The column list is placed in the unnamed
" buffer.
" GetColumnDef : Displays the column definition of the column name
" under the cursor. It assumes the CREATE TABLE
" statement is in an open buffer.
" CreateProcedure : Creates a stored procedure to perform standard
" operations against the table that the cursor
" is currently under.
Dependencies:
Align.vim - Version 15
- Author: Charles E. Campbell, Jr.
- https://www.vim8.org/script.php?script_id=294
|
| |
| install details |
Put <SQLUtilities.vim> into your .vim/plugin or _vimfiles/plugin directory
|
| |
script versions (upload new version)
Click on the package to download.
|
