SQL – Ryan Chapin's Website

PostgreSQL Query to Find Tables With Name LIKE

Posted on July 18, 2023 by rchapin

Sometimes you are working with a PostgreSQL database with A LOT of tables and looking for tables that contain a sub-string in their name. Following is a query that you can run that will return all of the tables that have the string in their name:

SELECT
  table_schema,
  table_name
FROM
  information_schema.tables
WHERE
      table_name LIKE '%<string%'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY
  table_name, table_schema
;

→ Continue reading

Unable to Set the Path To java.exe When Running Oracle SQL Developer Under Windows 7

Posted on May 22, 2014March 28, 2021 by rchapin

I was trying to run Oracle SQL Developer for the first time on a new machine. When firing it up, it presented me with a dialog box asking me to “Enter the full pathname for the java.exe file”.

OK, no problem. So I find the path to the java.exe binary that was just installed with the SDK. Hit submit . . . and nothing happens. It blanks out the text field and the dialog box stares back at me.

I → Continue reading “Unable to Set the Path To java.exe When Running Oracle SQL Developer Under Windows 7”

Executing Dynamically Generated SQL Queries in a Shell Script and Saving the Output to a Variable

Posted on January 10, 2014November 25, 2021 by rchapin

If you would like to, in a shell script, dynamically generate SQL queries for MySQL and save the output of those queries to a variable that you can then use in the script, here is an example:

#!/bin/bash

for i in `cat tables_list.txt`
do

   # Build the query
   QUERY="SELECT count(*) FROM ${i}"

   # Run the query from the command-line and save the
   # output into the $ROW_COUNT variable
   ROW_COUNT=$(echo $QUERY | mysql -u${USER_NAME} -p${PASSWORD} -h ${HOST} -P ${PORT} --skip-column-names ${DBASE})

→ Continue reading