MySQL – Stored Procedure – List Index Columns

Background

Wanted to start writing Stored Procedures against MySQL Databases.

Since we started talking about Indexes in MySQL, thought that will be

familiar grounds to start from.

 

Code

listIndexColumns

 


-- Create database dbUtility if it does not exist
CREATE database IF NOT EXISTS dbUtility;

-- use database dbUtility
use dbUtility;

-- Change delimiter from default of ; to //
delimiter //


DROP PROCEDURE IF EXISTS listIndexColumns;
//


-- Create Stored Procedure listIndexColumns
-- Parameter is dbname
create procedure listIndexColumns
(
	dbname nvarchar(100) 
)
begin

    /*
		Declare local variables
    */
	declare varID integer;
    declare varIDMax integer;
    
    declare varDBName NVARCHAR(100);
    declare varDBNameCurrent NVARCHAR(100);
    
    declare varTable NVARCHAR(100);
    declare varIndex NVARCHAR(100);
    declare varColumnList NVARCHAR(4000);


	declare varSeq integer;
    declare varColumn Nvarchar(100);
    declare varColumnID integer;
    declare varColumnIDMax integer;
 
    declare varColSeparator varchar(30);
    
    /*
		Drop Temporary tables if left over from previous run
    */
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;

    /*
       Create temporary table tblIndex
    */
	create temporary table tblIndex
	(
		id int not null 
		   auto_increment 
		   primary key

		 , tableName varchar(255) not null
		  
		 , indexName varchar(255) not null 
	
         , columnList varchar(4000) null
         
    )
	;

    /*
       Create temporary table tblIndex Column
    */
	create temporary table tblIndexColumn
	(
		   columnID   smallint not null 

         , columnName nvarchar(100) null
         
    )
	;

    /*
		set separator
    */
	set varColSeparator := ' / ';
        
    -- get Current Database
	set varDBNameCurrent := database();
    
    /*
		If dbName is passed in, then please use it
    */
    if (
			   ( dbName is null) 
            or ( dbName = '')
	   ) then 		
        
		set varDBName = varDBNameCurrent;        

   else
   
		set varDBName = dbName;
    
    end if;
    
    /*
		Get Tables & Index in current database
    */
	insert into tblIndex
	(
		   tableName 
		 , indexName  
	)
	select distinct
				TABLE_NAME
			  , iNDEX_NAME

	from   INFORMATION_SCHEMA.STATISTICS

	where  TABLE_SCHEMA = varDBName

    order by
    
		   	TABLE_NAME
            
		  ,	case 
              when INDEX_NAME = 'PRIMARY' then ''
              else INDEX_NAME
            end  
            
		  , iNDEX_NAME
   ;


    /* Reset table counter */
	set varID := 1;
    
    /* Count number of Tables / Indexes */
    set varIDMax := ( 
                        select max(id) 
                        
                        from tblIndex
					);

    /* Reset column list */
    set varColumnList = null;

    /*
		Iterate Tables & Indexes in current database
    */
	WHILE  varID <= varIDMax DO

        /*
			Get Table and Index Name into local variable
        */
		select 
              tableName, indexName
        
        into  varTable, varIndex
        
        from  tblIndex tblI
        
        where  tblI.id = varID
        ;

        /*
			Clear Temporary table use for caching
        */
        
        /*
			delete 
			
			from   tblIndexColumn
            
			;
        
        */
        
        truncate table tblIndexColumn;
        

        /*
			Get Columns for current table / index
        */
		insert into tblIndexColumn
		(
		      columnID 
            , columnName
		)
		select 
                     tblS.SEQ_IN_INDEX
                   , tblS.COLUMN_NAME
 		 
		from   INFORMATION_SCHEMA.STATISTICS tblS

		where  TABLE_SCHEMA = varDBName

		and    TABLE_NAME   = varTable

		and    INDEX_NAME   = varIndex
		;
        

    
		/*
			Reset Column ID
        */
		set varColumnID := 1;

        /*
			Reset Column List
        */
		set varColumnList := '';

    
		/*
			Get Number of Columns for Context Table / Index
        */
		set varColumnIDMax := 
                ( 
					select max(columnID) 
                
					from tblIndexColumn
				);

        /*
			Iterate Column List
        */
		while (varColumnID <= varColumnIDMax) do
			
		
            set varColumn := null;
            
			select 
					columnName
			INTO 
					varColumn
				   
			from   tblIndexColumn tblIC

			where  tblIC.columnID = varColumnID
		
			;  		
	   
			/*
				Add Column to Column List
			*/
			set varColumnList := Concat(
											  varColumnList
                                              
                                            , varColumn
                                            
                                            , case
                                                when (varColumnID < varColumnIDMax )
                                                     then varColSeparator
                                                else ''
                                              end  
										);


			/*
				Update Column Pointer
			*/
			set varColumnID := varColumnID + 1;
			
			
        end while
        ;
        

		/*
			Save Column List
		*/
		update tblIndex
          
        set    columnList = varColumnList
          
        where  id = varID
		; 
          
		
		/*
			Go to next table/index
        */
	    SET varID := varID + 1;
  

	END WHILE;
  
  
    /*
		Get resultset
    */
    select 
    	   tblI.id

		 , tblI.tableName 
		  
		 , tblI.indexName
	
         , tblI.columnList

    from   tblIndex tblI
   
	;
    
    
    /*
		Clean up - Drop Temporary Tables
    */
    
	drop temporary table if exists tblIndex;

	drop temporary table if exists tblIndexColumn;


end;

//


 

Lab


set @database = 'sakila';

call dbUtility.listIndexColumns(@database);

 

GitHub

Availed through GitHub here:

DanielAdeniji/MySQL.listIndexColumns
Link

 

Listening

Listening to my favorite person in the world when I am trying…

Joshua Radin
No Envy, No Fear
Link

 

Lyrics

Some are reaching, few are there
Want to reign from a hero’s chair
Some are scared to fly so high
Well this is how we have to try

When your sister turns to leave
Only when she’s most in need
Take away the cause of pain
By showing her we’re all the same

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s