Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is headache of actually typing out all 99 required columns!! Solutions / Ideas to above problem are: Ignorance is bliss. Select all(*) and ignore the column. Manually type column names or manage it with copy paste! Create […]
Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some […]
Looking for instances particular column in a large schema can be a pain. Fortunately the information schema makes this pretty easy, if your columns have a consistent naming convention.
SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%some_name%';
Now, if we want to wrap this up into an easy to use stored procedure, we can do something like this:
drop procedure find_column; delimiter // CREATE PROCEDURE find_column(c varchar(255)) begin SET @a = CONCAT("%", c, "%"); SELECT table_schema, table_name, column_name, column_type FROM information_schema.columns WHERE column_name LIKE @a; end // delimiter ;
We need to use the concat statement in order to properly get the quotes in there without using the literal string “c” in the LIKE statement.
You can do a search as follows:
CALL …[Read more]
This procedure will split a “;” separated column in to new fields preserving ids. This is very specific problem, lets check it with example. Consider a sample table test: And we want output as follows: So again I wrote a procedure. Procedure will read a data from “tmp” table and will split data by my_delimiter […]
Well procedures mainly carried out working with information schema and it’s usage in stored procedure. Procedures are fairly simple and easy to understand. 1. Edit_table – following procedure executes queries to particular table if it exists. Basically I created it to satisfy a need of altering a table if column exists. Now it can be used […]
This procedure lists available database objects under passed database name. It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database. It also lists storage engine of tables. It uses information schema database to gather information and storing in a temporary table. Usage: call xplore(database-name); – Procedure will search through information schema for […]
In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance. We have global buffers which are allocated irrespective of connections as and when mysql server is started. Along with […]
What will you do if one day some one ask you to find single string in all databases, all tables and in all columns? I just read such question and tried to find a “ready made” solution. Reusability is Key Concept !! But I ended up finding no “copy-paste” material. Some of the posts like http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm helped me […]
It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!
Here is a small stored procedure which does exactly that!
– shows all processes owned by username@hostname
– kills all processes owned by username@hostname
The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.