Feb 22, 2012

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.

Feb 14, 2012

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:


Feb 12, 2012

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.

Feb 10, 2012

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 

Feb 9, 2012

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