Mysql stored procedure to alter mutiple tables structure -


i'm building small script function or stored procedure able alter table structure in tables has no column named 'address'.

if table doesn't have column address script add it: 

alter table xxxx add column address varchar(150) null after command;

so far have found way access tables list has column 'address' on structure querying information_schema.columns table; such thing not need because need opposite, tables list has not column named 'address' on structure.

so script needs this:

alter table  (select table_name information_schema.columns table_schema = 'mydatabase'  , table_name 'mytable_%' , column_name = 'address')  add column if not exists address  varchar(150) null after command;

something need, needs validation of column name address , attempt add column if not exists, otherwise return exception , query die.

php or other programming language cannot used because more 4000 tables upgraded , http request die before database finish operations.

another possibility handle exception in case of existence of column named 'address' able continue operations no matter if exception returns, continue query execution tables without exceptions updated....

you have build dynamic sql this. best bet

ootomh code

select 'alter table ' + tablename ' + add address varchar (150)' information_schema.tables t not exists ( select 1 information_schema.columns c c.tablename = t.tablename , c.columnname = 'address' )

now have generated code, can execute hand.

if isn't on daily basis, there isn't of need deploy stored proc, there?

if really want it, have plethora of options - add string variable, loop through set cursor, etc.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more