Dynamically checking rules in database tables using SQL
I want to check for rows in a table where all values (except the key) is empty. I am using MySQL 5.5.
I plan to do this mechanically, so the approach should work for any table in my database schema.
Suppose for illustration purposes I start with the following table:
Then my initial steps are:
This yields:
How do I use this result to be able to construct and execute a statement as follows?:
That's obviously pseudo-code for illustration purposes, but the idea is that the approach should work for all tables.
I tried something as follows:
but that was a little too clever and resulted in an error message. Within the CONCAT a nested prepare-statement might work, but the syntax evades me.
So, how can I dynamically check for rows having all nulls?
... So how do I then execute the generated SELECT statements?
(1) Create a script to generate such queries.
(2) Pass the script to the mysql command and redirect the output to a file.
(3) And then pass the queries.sql containing the generated queries to the mysql command.
Yes, I was contemplating the export and then an import also. I see that more of a last resort option, because it involves writing to a file together with all its shortcomings.
The other solution we are investigating is through the use of cursors. This looks roughly as follows:
- create a list of all tables in the schema
- loop through these tables to create a list of all columns per table which are not keys
- loop through these columns to pick up rows where all values are NULL
Disadvantage here is that it requires the use of a procedural language as well as being less portable to other database systems.
Are you saying it is impossible to do entirely within MySQL in (possibly nested) SQL statements?
Hi,
Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out... (1 Reply)
I haven't coded in ksh in forever and this script is proving me not a great coder. I keep getting unexpected end of file when I try to execute this...Line 94 is the last line of the code..
#!/usr/bin/ksh
. /home/oraprod/.bash_profile
DBSID=prod # Database sid
CONNECTSTR=apps/xxxxxxxx@prod #... (6 Replies)
Hi all,
I am executing shell script in which I am using SQLLDR
In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following
Spool... (2 Replies)
I'm pretty new to the database world and I've run into a mental block of sorts. I've been unable to find the answer anywhere. Here's my problem: I have several tables and everything is as normalized as possible (as I've been lead to understand normalization.) Normalization has lead to some... (1 Reply)
I am coding shell script.
I need to connect to different databases like DB2, Oracle and Sybase.
I would then need to query tables where it has all the groups, users for that database.
I would also need who has what kind of permissions.
EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
Hi all, i would like to set a cronjob to back up my db tables.
I have written the par file, i would like to know how can i execute a exp command in a shell script.
my par file userid=admin/password
file=backup.dmp
log=backupLOG.log
tables=
(Tables 1,
Tables 2)My cron job script will be... (1 Reply)
I want to collect the database tables(Ex: Emp,Sal,Bonus...etc)
in a file & give this file at the command prompt, when prompted for the tables list at command prompt. How can i do that ?
Ex:- Import jason/jason1 tables=emp,sal,bonus log=j1.log
i want to change this into
Ex:- Import... (3 Replies)