Mysql script to get all duplicate indexes in a database

As we know indexes are good but over indexes are bad for DML statements. If there are multiple people are working are a same project there good probability of duplicate indexes. So first of all I would like to explain what is meaning of duplicate indexes.    

Meaning of duplicate index:

Consider on following two indexes:

1. CREATE INDEX NCI_One(a,b);

2. CREATE INDEX NCI_Two(b,a);

Both indexes are not duplicate indexes because in an index order of column matters.  So both indexes NCI_One and NCI_Two  are totally different indexes not duplicate indexes.

Consider on following four indexes:

1. CREATE INDEX NCI_One(a);

2. CREATE INDEX NCI_Two(a,b);

3. CREATE INDEX NCI_Three(a,b,c);

4. CREATE INDEX NCI_Four(b,c);

In the above example, NCI_One and NCI_Two are two duplicate indexes since it is subset of index NCI_Three. NCI_Four is also subset of index NCI_Three but it is not a duplicate index since it is not subset from first column.

Mysql script to get or list out all the duplicate indexes in database:

SELECT

      tblAllIndex.TABLE_SCHEMA AS DatabaseName,

      tblAllIndex.TABLE_NAME AS TableIndex,

      tblAllIndex.INDEX_NAME AS IndexName,

      tblDuplicateIndex.INDEX_NAME AS DuplicateIndexName,

      CONCAT('DROP INDEX `',tblDuplicateIndex.INDEX_NAME,'` ON `',tblDuplicateIndex.TABLE_SCHEMA,'`.`', tblDuplicateIndex.TABLE_NAME,'`;') AS DropDuplicateIndexScript

     

FROM

(

      SELECT

            TABLE_SCHEMA,

            TABLE_NAME,

            INDEX_NAME,

            GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) KeyList

      FROM information_schema.STATISTICS

      GROUP BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME

) tblAllIndex

INNER JOIN (

      SELECT

            TABLE_SCHEMA,

            TABLE_NAME,

            INDEX_NAME,

            GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) KeyList

      FROM information_schema.STATISTICS

      GROUP BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME

) tblDuplicateIndex

ON tblAllIndex.TABLE_SCHEMA = tblDuplicateIndex.TABLE_SCHEMA

AND tblAllIndex.TABLE_NAME = tblDuplicateIndex.TABLE_NAME

AND tblAllIndex.INDEX_NAME <> tblDuplicateIndex.INDEX_NAME

WHERE  tblDuplicateIndex.KeyList = LEFT(tblAllIndex.KeyList,LENGTH(tblDuplicateIndex.KeyList))

ORDER BY tblAllIndex.TABLE_SCHEMA,tblAllIndex.TABLE_NAME

Last column also keep the scrip to delete all the duplicates indexes from your database.

Sql query to get table data output in HTML format in sql server

Sql query to get table data output in HTML format in sql server

We can easily convert output of sql queries in html format. For this we have to create a stored procedure ConvertTableToHtml

Stored Procedure: 

CREATE PROCEDURE [dbo].[ConvertTableToHtml](

      @SqlQuery AS NVARCHAR(4000),    

         @FilePath AS VARCHAR(100)

)

AS

     

         DECLARE @Html AS VARCHAR(MAX) = ''

         DECLARE @Command AS VARCHAR(8000) = ''

      DECLARE @Header AS NVARCHAR(MAX) = ''

      DECLARE @Column AS NVARCHAR(MAX) = ''

      DECLARE @Query AS NVARCHAR(MAX)

      DECLARE @Css AS VARCHAR(MAX) = '

            <style type="text/css">

            table.gridtable {

                font-family: verdana,arial,sans-serif;

                font-size:11px;

                color:#333333;

                border-width: 1px;

                border-color: #666666;

                border-collapse: collapse;

            }

            table.gridtable th {

                border-width: 1px;

                padding: 8px;

                border-style: solid;

                border-color: #666666;

                background-color: #dedede;

            }

            table.gridtable td {

                border-width: 1px;

                padding: 8px;

                border-style: solid;

                border-color: #666666;

                background-color: #ffffff;

            }

            </style>

            '

BEGIN

      SET @Query = 'SELECT * INTO ##columns FROM ( ' + @SqlQuery + ') Temp'

      EXECUTE(@Query)

    SELECT @Column = @Column + 'ISNULL( CONVERT( VARCHAR(MAX),' + QUOTENAME(name ) + ',1)  ,'' '')' + ' AS TD , '

       --SELECT @Column = @Column + QUOTENAME(name) + ' AS TD, '

      FROM tempdb.SYs.columns

      WHERE object_id = OBJECT_ID('tempdb..##columns')

    

      SET  @Column = LEFT(@Column,LEN(@Column)-1)

      SELECT @Header = @Header + '<TH>' +  name + '</TH>'

      FROM tempdb.SYs.columns

      WHERE object_id = OBJECT_ID('tempdb..##columns')

    

      SET @Header = '<TR>' + @Header  + '</TR>'

    

      SET @Query = 'SET  @Html = (SELECT ' + @Column + ' FROM ( ' + @SqlQuery + ') AS TR

       FOR XML AUTO ,ROOT(''TABLE''), ELEMENTS)'

        

      EXECUTE SP_EXECUTESQL @Query,N'@Html VARCHAR(MAX) OUTPUT',@Html OUTPUT

      SET  @Html = @Css + REPLACE(@Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @Header)

      DROP TABLE ##columns

         SELECT @Html AS #Html INTO #HtmlTable

       

         SET @Command = 'bcp "select * FROM #HtmlTable" queryout "' + @FilePath + '" -c -t"," -r"\n" -T'

         exec master..xp_cmdshell @Command

      

END

To execute:

DECLARE @Html AS VARCHAR(MAX)

EXECUTE ConvertTableToHtml ' SELECT TOP(10) * FROM Departments ',@Html OUTPUT

SELECT @Html

Sample output:

<style type="text/css">

    table.gridtable

    {

        font-family: verdana,arial,sans-serif;

        font-size: 11px;

        color: #333333;

        border-width: 1px;

        border-color: #666666;

        border-collapse: collapse;

    }

    table.gridtable th

    {

        border-width: 1px;

        padding: 8px;

        border-style: solid;

        border-color: #666666;

        background-color: #dedede;

    }

    table.gridtable td

    {

        border-width: 1px;

        padding: 8px;

        border-style: solid;

        border-color: #666666;

        background-color: #ffffff;

    }

</style>

<table class="gridtable">

    <tr>

        <th>

            deptid

        </th>

        <th>

            deptname

        </th>

        <th>

            deptmgrid

        </th>

    </tr>

    <tr>

        <td>

            1

        </td>

        <td>

            HR

        </td>

        <td>

            2

        </td>

    </tr>

    <tr>

        <td>

            2

        </td>

        <td>

            Marketing

        </td>

        <td>

            7

        </td>

    </tr>

    <tr>

        <td>

            3

        </td>

        <td>

            Finance

        </td>

        <td>

            8

        </td>

    </tr>

    <tr>

        <td>

            4

        </td>

        <td>

            R&amp;D

        </td>

        <td>

            9

        </td>

    </tr>

    <tr>

        <td>

            5

        </td>

        <td>

            Training

        </td>

        <td>

            4

        </td>

    </tr>

    <tr>

        <td>

            6

        </td>

        <td>

            Gardening

        </td>

        <td>

            0

        </td>

    </tr>

</table>

Screenshot:

CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

When we will create Assembly in sql sever we may get error message like this:

CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Solution:

Execute following sql statement in your database

ALTER DATABASE DemoDB SET TRUSTWORTHY ON

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Note: Replace the DemoDB by your database name.

How to create stored procedure in sql server management studio (SSMS)

In sql server it is very easy to create stored procedure. We can create a stored procedure in sql server management studio (SSMS) in following way:

Creating stored procure using server management studio (SSMS)

Step 1: Expand your database name node in SSMS. Then expand Programmability node as shown in the following screenshot:

Step 2: Right click on Stored Procedures node and click on New stored procedure...

Step 3: It will generate following sql script in new query page:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       <Author,,Name>

-- Create date: <Create Date,,>

-- Description:  <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

    -- Add the parameters for the stored procedure here

    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

Step 4: Now edit above script like stored procedure name, parameters, procedure body etc according to your requirement . For example:

-- =============================================

-- Author:       Exact Help

-- Create date: 02/10/2012

-- Description:  My first stored procedure

-- =============================================

CREATE PROCEDURE My_First_Proc

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT 'Exact Help'

END

GO

Step 5: To create My_First_Proc stored procedure press F5 button of your keyboard or click on Execute button in SSMS.

Step 6: After creating it will be stored inside Programmability node. To re-open or edit it expand programmability node and right click on your stored procedure name and choose modify option.

You will get the script of your stored procedure. In this case it will be script of My_First_Proc. Which will something like this:

USE [Exact]

GO

/****** Object:  StoredProcedure [dbo].[My_First_Proc]    Script Date: 02/10/2012 10:02:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:       Exact Help

-- Create date: 02/10/2012

-- Description:  My first stored procedure

-- =============================================

ALTER PROCEDURE [dbo].[My_First_Proc]

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT 'Exact Help'

END 

Vb.net code to compress and decompress any file

Vb.net code to compress any file

Imports System.IO.Compression

Imports System.IO

Module Module1

#Region "Global variable"

    Dim GzOut As GZipStream

    Dim Sr As StreamReader

    Dim Sw As StreamWriter

#End Region

    Public Sub Main()

        'Compression code

        Try

            'Create a compressed stream using a new file

            GzOut = New GZipStream(File.Create("C:\SSIS\data.zip"), CompressionMode.Compress)

            'Create a StreamWriter object to allow writing strings to the GZipStream

            Sr = New StreamReader("C:\SSIS\data.txt")

            Sw = New StreamWriter(GzOut)

            Sw.Write(Sr.ReadToEnd)

        Catch ex As Exception

            Console.WriteLine(ex.Message)

        Finally

            'Close the stream objects

            Sw.Close()

            Sr.Close()

            GzOut.Close()

        End Try

    End Sub

End Module

Vb.net code to decompress a zip format file

Imports System.IO.Compression

Imports System.IO

Module Module1

#Region "Global variable"

    Dim Sr As StreamReader

    Dim GzIn As GZipStream

#End Region

    Public Sub Main()       

Try

            'Open the file containing the compressed data

            GzIn = New GZipStream(File.OpenRead("C:\Documents and Settings\riteshk\My Documents\data\data.zip"), CompressionMode.Decompress)

            'Read and display the compressed data

            Sr = New StreamReader(GzIn)

            Console.WriteLine(Sr.ReadToEnd())

        Catch ex As Exception

            Console.WriteLine(ex.Message)

        Finally

            'Close the stream objects

            Sr.Close()

            GzIn.Close()

        End Try

        Console.ReadKey()

    End Sub

End Module