Archive for the ‘Database’ Category

DROP multiple TABLE / TRIGGER / FUNCTON in oracle using PL SQL

To drop more than one tables having a matching criteria then we should use a PL SQL statement, in oracle there is a table called USER_OBJECTS having name of all the tables for that particular user.

DROP TABLE is a DDL statement so that we need EXECUTE IMMEDIATE privilege for dropping table from a script, more easy way to drop table is to generate DROP TABLE command using a script and run that command.

The following PL SQL script generate drop table command for all tables having table name contains  word 'test'

SET SERVEROUTPUT ON;

DECLARE
  object_name varchar2(100);
  cursor cur1 is SELECT object_name FROM user_objects WHERE object_type='TABLE' AND object_name LIKE '%test%';
BEGIN

  open cur1;
  loop
        fetch cur1 into object_name;
        exit when cur1%notfound;
      dbms_output.put_line('DROP TABLE ' || object_name || ';' );
    end loop;
    close cur1;

END ;

Output

anonymous block completed

DROP TABLE USER_TEST;

Read more »

Find table names and column names inside a database using oracle

Display all table names in a database

select * from tab;


Display number of tables in a database

select count(*) from tab;


Find table exits in a database

select * from tab where tname like '%STUDENT%';


Display all column names in a database

select * from USER_TAB_COLUMNS;


Display number of columns in a database

select count(*) from USER_TAB_COLUMNS;


Find column name exits in a database

select * from USER_TAB_COLUMNS where COLUMN_NAME like '%STUDENT_ID%';


Find column name exits on a table in a database

select * from USER_TAB_COLUMNS where TABLE_NAME like '%STUDENT%' and COLUMN_NAME like 'STUDENT_ID';


 

Read more »

Create and grant privileges to user in MySQL

Create user in MySQL

CREATE USER 'jijokjose'@'localhost' IDENTIFIED BY 'jijokjose';


Grant all privileges to user in MySQL

GRANT ALL PRIVILEGES ON *.* TO 'jijokjose'@'localhost' WITH GRANT OPTION;


Create database in MySQL

CREATE DATABASE db_vc;


Login using CMD

mysql –user=jijokjose –password=jijokjose db_name


Import database file using CMD

H:\>mysql -u jijokjose -p db_vc < H:\\db_vc.sql
Enter password: *********


Read more »