MySQL data files .MYD, .MYI, and .frm

A MySQL database creates a number of different data files in the mysql data directory (typically /var/lib/mysql). The following are the differences between the data files created:

File Extension	Purpose
.frm	table definition
.MYD	table data
.MYI	table indices

I believe these files are created for both MyISAM and InnoDB table types because the MySQL documentation references these types with respect to MyISAM and I definitely see them on my InnoDB tables.
I couldn’t find this information directly in the MySQL documentation and google wasn’t much help either so hopefully this post will save someone else some search effort.

In windows these files locate at:

"C:\ProgramData\MySQL\MySQL Server 5.*\data"

Create procedure, Cursor example , execute immediate

Create procedure in mysql,  Cursor example in mysql, execute immediate in mysql

cursor/procedure example

DELIMITER ##

CREATE PROCEDURE cursorexam()

BEGIN

    DECLARE vempno BIGINT; 

    DECLARE vsal BIGINT;

    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR SELECT empno ,sal FROM emp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP

    FETCH cur INTO vempno,vsal;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO empchart (empno, sal, pcat) VALUES (vempno, vsal, 1);

    END LOOP;

    CLOSE cur;

END;##

Execute immediate in mysql

DELIMITER $$

CREATE PROCEDURE Exec_imme_ex()
BEGIN

DECLARE tName TEXT;
DECLARE tSchema TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
    select table_name,table_Schema from information_schema.tables where table_schema='scott';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
FETCH cur INTO tName,tSchema;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET @s = CONCAT('insert into tab values (''',tName, ''',''', tSchema, ''')');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END LOOP;
CLOSE cur;
deallocate prepare stmt;
END
$$

DELIMITER ;



continue to post:

Dynamic Cursor in mysql

Unused index MySql

Unused index MySql
Find --


logic is here


SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME,
    s.SEQ_IN_INDEX,
    ( SELECT MAX(SEQ_IN_INDEX)
    FROM INFORMATION_SCHEMA.STATISTICS s2
    WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s.TABLE_NAME = s2.TABLE_NAME
    AND s.INDEX_NAME = s2.INDEX_NAME
    ) AS `COLS_IN_INDEX`,
    s.CARDINALITY AS `CARD`,
    t.TABLE_ROWS AS `ROWS`,
    ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %`
FROM INFORMATION_SCHEMA.STATISTICS s
    INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA != 'mysql'
    AND t.TABLE_ROWS > 10
    AND s.CARDINALITY IS NOT NULL
    AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY `SEL%`, TABLE_SCHEMA, TABLE_NAME;

Translate Equivalent in Sqlserver

translate function in Sqlserver

source - http://www.dbforums.com/microsoft-sql-server/1216565-oracle-translate-function-equivalent-sql-server.html

CREATE FUNCTION dbo.Thicky_Pants 
 (
 @Input AS VarChar(1000),
 @Find AS VarChar(100),
 @Replace AS VarChar(100)
 )
 RETURNS VarChar(1000)
AS
BEGIN
 
 DECLARE @i AS TinyInt
 
 SELECT @i = 1
 
 WHILE @i <= LEN(@Find) BEGIN
  
  SELECT @Input = REPLACE(@Input, SUBSTRING(@Find, @i, 1), SUBSTRING(@Replace, @i, 1))
  SELECT @i = @i + 1
 
 END
 RETURN @Input
END
GO
 
DECLARE @String AS VarChar(1000)
 
SELECT @String = 'pootle_flump'
SELECT @String = dbo.Thicky_Pants(@String, 'pt', 'xz')
 
PRINT  @String

Lpad & Rpad in sqlserver

Lpad & Rpad in sqlserver

 
DECLARE @vstr VARCHAR(3)
SET @vstr = 'paddig'
SELECT
RIGHT(REPLICATE(' ',100) + @test ,10) AS RPAD,
LEFT(@test + REPLICATE(' ',100) ,10) AS LPAD

or

select RIGHT(REPLICATE(' ',100) + 'padstr' ,10) AS RPAD + LEFT('padstr' + REPLICATE(' ',100) ,10) AS LPAD