Get all non-clustered indexes

DECLARE cIX CURSOR FOR
    SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
        FROM Sys.Indexes SI
            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
        WHERE TC.CONSTRAINT_NAME IS NULL
            AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
        ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
    SET @IXSQL = 'CREATE '

    -- Check if the index is unique
    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
        SET @IXSQL = @IXSQL + 'UNIQUE '
    -- Check if the index is clustered
    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
        SET @IXSQL = @IXSQL + 'CLUSTERED '

    SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

    -- Get all columns of the index
    DECLARE cIxColumn CURSOR FOR
        SELECT SC.Name
        FROM Sys.Index_Columns IC
            JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
        WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
        ORDER BY IC.Index_Column_ID

    DECLARE @IxColumn SYSNAME
    DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

    -- Loop throug all columns of the index and append them to the CREATE statement
    OPEN cIxColumn
    FETCH NEXT FROM cIxColumn INTO @IxColumn
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF (@IxFirstColumn = 1)
            SET @IxFirstColumn = 0
        ELSE
            SET @IXSQL = @IXSQL + ', '

        SET @IXSQL = @IXSQL + @IxColumn

        FETCH NEXT FROM cIxColumn INTO @IxColumn
    END
    CLOSE cIxColumn
    DEALLOCATE cIxColumn

    SET @IXSQL = @IXSQL + ')'
    -- Print out the CREATE statement for the index
    IF @IXSQL != '' BEGIN PRINT @IXSQL END

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

SQL SERVER – ReIndexing Database Tables

DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats

Calculate number of months between two dates using javascript

var dtFrom= new Date('01/05/2019');
var dtTo= new Date('01/06/2021');

 var fromYear;
    var fromMonth = dtFrom.getMonth() + 1;
    var toYear;
    var toMonth = dtTo.getMonth() + 1;

    if ((fromYear = dtFrom.getFullYear()) < (toYear = dtTo.getFullYear())) {
        toMonth += (toYear - fromYear) * 12;
    }
    var diffMonths = toMonth - fromMonth;
    if (dtFrom.getDate() > dtTo.getDate()) diffMonths--;
   
-----------------------------------------------------------------------------
For get only the month diff

var dtFrom= new Date('01/05/2019');
var dtTo= new Date('01/06/2021');

 var monthCount = 0;
    while ((dtFrom.getMonth() + '' + dtFrom.getFullYear()) != (dtTo.getMonth() + '' + dtTo.getFullYear())) {
        monthCount++;
        dtFrom.setMonth(dtFrom.getMonth() + 1);
    }
    monthCount  = monthCount + 1;

Remove special characters in a particular column

DECLARE @myString varchar(500), @validChars varchar(100)
SELECT @myString = 'sA123-\[]{}',@validChars = 'a-z0-9'

While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

SELECT  @myString AS OutPutString

How can I parse JSON with C#?

var jData = "{\"validationErrors\":[{\"description\":\"Employer Registration Number not found.\",\"code\":\"1003\",\"path\":\"employerRegistrationNumber\"}]}";

 var varData = JObject.Parse(jData);
            var varValidationErrorDetails = varData["validationErrors"];
            var varValidationErrorDescription = varValidationErrorDetails[0]["description"];
            string strValidationErrorDescription = Convert.ToString(varValidationErrorDescription).Replace("\r\n", "").Trim();

Auto Query Generator In MSSQL Server

Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

• Need to maintain a separate stored procedure to every table
• Don’t use * in the query instead specify the column
• Use the correct data type and size of a column
• Every parameter should be nullable in a stored procedure.

CREATE PROC [dbo].[USP_QueryCreationSupport] (@table_Name varchar(100))
AS
BEGIN
    DECLARE @InserCols NVARCHAR(max)
    DECLARE @Inserparam NVARCHAR(max)
    DECLARE @Insertquery NVARCHAR(max)
    DECLARE @Selectquery NVARCHAR(max)
    DECLARE @Update NVARCHAR(max)
    DECLARE @DeleteQuery NVARCHAR(max)

    SELECT '@' + c.NAME + Space(1) + Case cast(t.Name as nvarchar(40))
                                         WHEN 'nvarchar' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'varchar' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'char' THEN
                                             t.NAME + '(' + cast(c.max_length as nvarchar(30)) + ')'
                                         WHEN 'decimal' THEN
                                             t.NAME + '(18,2)'
                                         ELSE
                                             t.name
                                     end + ' = null,' AS Params
FROM sys.columns c
        inner join sys.types t
            ON c.user_type_id = t.user_type_id
    WHERE c.object_id = object_id(@table_Name)

    SET @InserCols =
    (
        select distinct
            (
                select sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )
    SELECT @InserCols = LEFT(@InserCols, Len(@InserCols) - 1)

    SET @Inserparam =
    (
        select distinct
            (
                select '@' + sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )


    SELECT @Inserparam = LEFT(@Inserparam, Len(@Inserparam) - 1)

    SET @Insertquery = 'insert into ' + @table_Name + ' (' + @InserCols + ')' + ' values ' + ' (' + @Inserparam + ')'

    SELECT @Insertquery AS InsertQuery


    SET @Update =
    (
        select distinct
            (
                select sc.NAME + '= @' + sc.NAME + ','
                FROM sys.tables st
                    inner join sys.columns sc
                        ON st.object_id = sc.object_id
                WHERE st.NAME = @table_Name
                for xml path(''), type
            ).value('.', 'NVARCHAR(MAX)')
    )

    SELECT @Update = LEFT(@Update, Len(@Update) - 1)

    SET @Update = 'Update ' + @table_Name + ' set ' + @Update

    SELECT @Update AS UpdateQuery

    SET @Selectquery = 'select ' + @InserCols + ' from ' + @table_Name

    SELECT @Selectquery AS SelectQuery
END

--Execute the stored procedure and pass your table name as a parameter.
EXECUTE dbo.[USP_QueryCreationSupport] @table_Name = 'settings'

SQL SERVER – Custom TRIM Function

ALTER FUNCTION [dbo].[fn_Trim](@String NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String,CHAR(10),'[]'),CHAR(13),'[]'),char(9),'[]'),CHAR(32),'[]'),'][',''),'[]',CHAR(32))));
END

Get All Primary Key In SQL

-- Let's get the columns of the Primary key into a CTE 
;WITH mycte 
     AS (SELECT Schema_name(o.schema_id)            AS 'Schema', 
                Object_name(i2.object_id)           AS 'TableName', 
                Stuff((SELECT ',' + Col_name(ic.object_id, ic.column_id) 
                       FROM   sys.indexes i1 
                              INNER JOIN sys.index_columns ic 
                                      ON i1.object_id = ic.object_id 
                                         AND i1.index_id = ic.index_id 
                       WHERE  i1.is_primary_key = 1 
                              AND i1.object_id = i2.object_id 
                              AND i1.index_id = i2.index_id 
                       FOR xml path('')), 1, 1, '') AS PK 
         FROM   sys.indexes i2 
                INNER JOIN sys.objects o 
                        ON i2.object_id = o.object_id 
         WHERE  i2.is_primary_key = 1 
                AND o.type_desc = 'USER_TABLE') 

-- Use this select to get the count, join to the CTE and get the column list 

SELECT Schema_name(o.schema_id)                    AS 'Schema', 
       Object_name(i.object_id)                    AS 'TableName', 
       Count(Col_name(ic.object_id, ic.column_id)) AS 'Primary_Key_Column_Count' 
       , 
       mycte.pk                                    AS 
       'Primary_Key_Columns' 
FROM   sys.indexes i 
       INNER JOIN sys.index_columns ic 
               ON i.object_id = ic.object_id 
                  AND i.index_id = ic.index_id 
       INNER JOIN sys.objects o 
               ON i.object_id = o.object_id 
       INNER JOIN mycte 
               ON mycte.tablename = Object_name(i.object_id) 
WHERE  i.is_primary_key = 1 
       AND o.type_desc = 'USER_TABLE' 
GROUP  BY Schema_name(o.schema_id), 
          Object_name(i.object_id), 
          mycte.pk   
ORDER  BY 'TableName' ASC   

Ajax Call Freezes UI, Locked UI During Ajax Call

Solutions: 1. Change the logic to use asynchronous ajax calls.

This may require some re-factoring of the code and be time consuming
but is the best solution.




2. If decided to keep the ajax call synchronous then put this ajax call into
setTimeout to call it with delay and be able to show the animation of
any type (e.g. loading spinner) , this can be shown for some short moment
like 300 milliseconds before the UI will be locked by sync ajax call.




$('#go').click(function() { $(this).after('<div id="loading">Loading...</div>'); setTimeout(function() { $.ajax({ url: '/echo/html/', type: 'post', async: false, data: { html: '<p>AJAX DONE</p>', delay: 3 }, success: function(data) { console.log(data); $('#go').after(data); }, error: function() { alert('Error!'); }, complete: function() { $('#loading').remove(); } }); }, 0); });




help URL http://jsfiddle.net/ambiguous/zLnED/

Truncate Database All Table In SqlServer

DECLARE @tablas TABLE(tableName nvarchar(100))

INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ '.'+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T

DECLARE @loopCounter INT
SELECT @loopCounter = 0
SELECT @loopCounter = COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHILE @loopCounter>0 
BEGIN
    SELECT TOP 1 @tableName = tableName FROM @tablas 
    DECLARE @sql NVARCHAR(500) = ''

    SET @sql = @sql +'Truncate table  '+ @tableName
    EXECUTE (@sql)
    SELECT @sql
    SET @loopCounter = @loopCounter-1   
    DELETE TOP (1) @tablas 
END

Return Newly Inserted Row In Sql Server

CREATE TABLE #TT
(
    ID uniqueidentifier NOT NULL
        DEFAULT newID(),
    name varchar(20),
    AreaCode INT
)

INSERT INTO #TT
(
    Name,
    AreaCode
)
OUTPUT INSERTED.ID,
       INSERTED.Name,
       INSERTED.AreaCode
SELECT 'SS',
       1
UNION ALL
SELECT 'SS1',
       2

DROP TABLE #TT

Recompile All Table, Stored Procedures and Function In SQL Server

CREATE PROCEDURE [dbo].[spEXECsp_RECOMPILE]
AS
BEGIN
    DECLARE @TableName varchar(128)
    DECLARE @OwnerName varchar(128)
    DECLARE @CMD1 varchar(8000)
    DECLARE @TableListLoop int
    DECLARE @TableListTable table
    (
        UIDTableList int IDENTITY(1, 1),
        OwnerName varchar(128),
        TableName varchar(128)
    )

    INSERT INTO @TableListTable
    (
        OwnerName,
        TableName
    )
    SELECT u.[Name],
           o.[Name]
    FROM sys.objects o
        INNER JOIN sys.schemas u
            ON o.schema_id = u.schema_id
    WHERE o.type IN (   'U', -- table
                        'P', -- stored procedures
                        'FN' -- scalar functions
                    )
    ORDER BY u.[Name],
             o.[Name]

    SELECT @TableListLoop = MAX(UIDTableList)
    FROM @TableListTable

    WHILE @TableListLoop > 0
    BEGIN
        SELECT @TableName = TableName,
               @OwnerName = OwnerName
        FROM @TableListTable
        WHERE UIDTableList = @TableListLoop

        SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13)

        --PRINT @CMD1
        EXECUTE (@CMD1)

        SELECT @TableListLoop = @TableListLoop - 1
    END

    SELECT *
    FROM @TableListTable

    SET NOCOUNT OFF

END
------------------------------------------------------
--To run used
EXECUTE [dbo].spEXECsp_RECOMPILE

Custom serial number key generator in C# .NET

Create one class named RandomSNKGenerator

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace AppCode

{

    public enum SNKeyLength

    {

        SN16 = 16, SN20 = 20, SN24 = 24, SN28 = 28, SN32 = 32

    }
   /// <summary>
   /// Supports Only 12 Digit
   /// </summary>

    public enum SNKeyNumLength

    {

        SN4 = 4, SN8 = 8, SN12 = 12

    }

    public static class RandomSNKGenerator

    {

        private static string AppendSpecifiedStr(int length, string str, char[] newKey)

        {

            string

            newKeyStr = "";

            int k = 0;

            for (int i = 0; i < length; i++)

            {

                for(k = i; k < 4 + i; k++)

                {

                    newKeyStr += newKey[k];

                }

                if (k == length)

                {

                    break;

                }

                else

                {

                    i = (k) - 1;

                    newKeyStr += str;

                }

            }

            return newKeyStr;

        }

        public static string GetSerialKeyAlphaNumaric(SNKeyLength keyLength)

        {

            Guid newguid = Guid.NewGuid();

            string randomStr = newguid.ToString("N");

            string tracStr = randomStr.Substring(0,(int)keyLength);

            tracStr = tracStr.ToUpper();

            char[] newKey = tracStr.ToCharArray();

            string newSerialNumber = "";

            switch (keyLength)

            {

                case SNKeyLength.SN16:newSerialNumber = AppendSpecifiedStr(16, "-", newKey);

                    break;

                case SNKeyLength.SN20: newSerialNumber = AppendSpecifiedStr(20, "-", newKey);

                    break;

                case SNKeyLength.SN24: newSerialNumber = AppendSpecifiedStr(24, "-", newKey);

                    break;

                case SNKeyLength.SN28: newSerialNumber = AppendSpecifiedStr(28, "-", newKey);

                    break;

                case SNKeyLength.SN32: newSerialNumber = AppendSpecifiedStr(32, "-", newKey);

                    break;

            }

            return newSerialNumber;

        }

        public static string GetSerialKeyNumaric(SNKeyNumLength keyLength)

        {

            Random rn = new Random();

            double sd = Math.Round(rn.NextDouble() * Math.Pow(10, (int)keyLength) + 4);

            return sd.ToString().Substring(0, (int)keyLength);

        }

    }

}

------------------------------------------------------------

string serialNumber = RandomSNKGenerator.GetSerialKeyAlphaNumaric(SNKeyLength.SN16);

or if you want to generate random number only :

string serialNumberNumberOnly = RandomSNKGenerator.GetSerialKeyNumaric(SNKeyNumLength.SN12);

  string serialNumber = AppCode.RandomSNKGenerator.GetSerialKeyAlphaNumaric(SNKeyLength.SN20);

Add a datarow in datatable at specified index

DataTable existingDataTable = GetMeDataFromSomeWhere();
//Add a new row to table
DataRow newRow = existingDataTable.NewRow();

newRow["ID"] = 999;
newRow["SomeColumn"] = "Manas Bhardwaj";

existingDataTable.Rows.Add(newRow);

//OR If nedd row add in a specific row 

existingDataTable.Rows.InsertAt(dr, 0);

Indian Currency To Words In Sql Server

CREATE FUNCTION [dbo].[GetNumberToWords] (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @strNumberString VARCHAR(9)
    DECLARE @strReturn VARCHAR(2000)
    DECLARE @intUnits SMALLINT

    -- Alter table of number groups
    DECLARE @tblNumberGroups TABLE
    (
        Units SMALLINT,
        Hundreds SMALLINT,
        Tens SMALLINT
    )

    -- Handle errors and 'quick wins'
    IF @intNumberValue IS NULL
        RETURN NULL
    IF ISNUMERIC(@intNumberValue) = 0
        RETURN NULL
    IF @intNumberValue = 0
        RETURN 'ZERO'

    IF @intNumberValue < 0
    BEGIN
        SET @strReturn = 'MINUS '
        SET @intNumberValue = ABS(@intNumberValue)
    END

    SET @intUnits = 0

    -- Populate table of number groups
    WHILE (@intNumberValue % 1000) > 0 OR (@intNumberValue / 1000) > 0
    BEGIN
        INSERT INTO @tblNumberGroups
        (
            Units,
            Hundreds,
            Tens
        )
        VALUES
        (@intUnits, (@intNumberValue % 1000) / 100, (@intNumberValue % 1000) % 100)
        SELECT @intNumberValue = CAST(@intNumberValue / 1000 AS INTEGER)
        SET @intUnits = @intUnits + 1
    END

    -- Remove last unit added
    SET @intUnits = @intUnits - 1

    -- Concatenate text number by reading number groups in reverse order
    SELECT @strReturn
        = ISNULL(@strReturn, ' ')
          + ISNULL(
                      ISNULL((CASE Hundreds
                                  WHEN 1 THEN
                                      'ONE HUNDRED '
                                  WHEN 2 THEN
                                      'TWO HUNDRED '
                                  WHEN 3 THEN
                                      'THREE HUNDRED '
                                  WHEN 4 THEN
                                      'FOUR HUNDRED '
                                  WHEN 5 THEN
                                      'FIVE HUNDRED '
                                  WHEN 6 THEN
                                      'SIX HUNDRED '
                                  WHEN 7 THEN
                                      'SEVEN HUNDRED '
                                  WHEN 8 THEN
                                      'EIGHT HUNDRED '
                                  WHEN 9 THEN
                                      'NINE HUNDRED '
                              END
                             ),
                             ' '
                            ) + CASE
                                    WHEN (
                                             Hundreds > 0
                                             OR Units < @intUnits
                                         )
                                         AND Tens > 0 THEN
                                        ' AND '
                                    ELSE
                                        ' '
                                END + ISNULL(   (CASE Tens / 10
                                                     WHEN 2 THEN
                                                         'TWENTY '
                                                     WHEN 3 THEN
                                                         'THIRTY '
                                                     WHEN 4 THEN
                                                         'FORTY '
                                                     WHEN 5 THEN
                                                         'FIFTY '
                                                     WHEN 6 THEN
                                                         'SIXTY '
                                                     WHEN 7 THEN
                                                         'SEVENTY '
                                                     WHEN 8 THEN
                                                         'EIGHTY '
                                                     WHEN 9 THEN
                                                         'NINETY '
                                                 END
                                                ),
                                                ' '
                                            ) + ISNULL(   (CASE Tens
                                                               WHEN 10 THEN
                                                                   'TEN '
                                                               WHEN 11 THEN
                                                                   'ELEVEN '
                                                               WHEN 12 THEN
                                                                   'TWELVE '
                                                               WHEN 13 THEN
                                                                   'THIRTEEN '
                                                               WHEN 14 THEN
                                                                   'FOURTEEN '
                                                               WHEN 15 THEN
                                                                   'FIFTEEN '
                                                               WHEN 16 THEN
                                                                   'SIXTEEN '
                                                               WHEN 17 THEN
                                                                   'SEVENTEEN '
                                                               WHEN 18 THEN
                                                                   'EIGHTEEN '
                                                               WHEN 19 THEN
                                                                   'NINETEEN '
                                                           END
                                                          ),
                                                          ' '
                                                      )
                      + COALESCE(
CASE
WHEN Tens % 10 = 1
AND Tens / 10 <> 1 THEN
'ONE '
END,
CASE
WHEN Tens % 10 = 2
AND Tens / 10 <> 1 THEN
'TWO '
END,
CASE
WHEN Tens % 10 = 3
AND Tens / 10 <> 1 THEN
'THREE '
END,
CASE
WHEN Tens % 10 = 4
AND Tens / 10 <> 1 THEN
'FOUR '
END,
CASE
WHEN Tens % 10 = 5
AND Tens / 10 <> 1 THEN
'FIVE '
END,
CASE
WHEN Tens % 10 = 6
AND Tens / 10 <> 1 THEN
'SIX '
END,
CASE
WHEN Tens % 10 = 7
AND Tens / 10 <> 1 THEN
'SEVEN '
END,
CASE
WHEN Tens % 10 = 8
AND Tens / 10 <> 1 THEN
'EIGHT '
END,
CASE
WHEN Tens % 10 = 9
AND Tens / 10 <> 1 THEN
'NINE '
END, ' '
)
                      + COALESCE(
CASE
WHEN Units = 1
AND (
Hundreds > 0
OR Tens > 0
) THEN
'THOUSAND '
END,
CASE
WHEN Units = 2
AND (
Hundreds > 0
OR Tens > 0
) THEN
'MILLION '
END,
CASE
WHEN Units = 3
AND (
Hundreds > 0
OR Tens > 0
) THEN
'BILLION '
END,
CASE
WHEN Units = 4
AND (
Hundreds > 0
OR Tens > 0
) THEN
'TRILLION '
END, ' '),
                      ' '
                  )
    FROM @tblNumberGroups
    ORDER BY units DESC

    -- Get rid of all the spaces
    WHILE CHARINDEX('  ', @strReturn) > 0
    BEGIN
        SET @strReturn = REPLACE(@strReturn, '  ', ' ')
    END

    SET @strReturn = LTRIM(RTRIM(@strReturn))

    RETURN @strReturn
END

GO
----------------------------------********************************----------------------------------------

CREATE FUNCTION [dbo].[GetNumericToRupees] (@RUPEES AS DECIMAL(30, 2))
RETURNS VARCHAR(2000)
AS
BEGIN
    DECLARE @INNTBL_01 TABLE
    (
        RECNO INT IDENTITY(1, 1),
        MTEXT NVARCHAR(50)
    )

    DECLARE @INNTBL_02 TABLE
    (
        RECNO INT IDENTITY(1, 1),
        MTEXT NVARCHAR(50)
    )

    DECLARE @AMOUNT NUMERIC(38, 0)

    DECLARE @PAISE AS INT

    SET @AMOUNT = FLOOR(@RUPEES)

    SET @PAISE = CAST(CONVERT(DECIMAL(10, 0), (@RUPEES % 1) * 100) AS INT)


    --STEP 2:

    --NOW ADD UP THE SALT AND PEPPER ONTO TABLE  I.E. INSERT THE WORDS INTO IT

    INSERT INTO @INNTBL_01
    SELECT 'One '

    INSERT INTO @INNTBL_01
    SELECT 'Two '

    INSERT INTO @INNTBL_01
    SELECT 'Three '

    INSERT INTO @INNTBL_01
    SELECT 'Four '

    INSERT INTO @INNTBL_01
    SELECT 'Five '

    INSERT INTO @INNTBL_01
    SELECT 'Six '

    INSERT INTO @INNTBL_01
    SELECT 'Seven '

    INSERT INTO @INNTBL_01
    SELECT 'Eight '

    INSERT INTO @INNTBL_01
    SELECT 'Nine '

    INSERT INTO @INNTBL_01
    SELECT 'Ten '

    INSERT INTO @INNTBL_01
    SELECT 'Eleven '

    INSERT INTO @INNTBL_01
    SELECT 'Twelve '

    INSERT INTO @INNTBL_01
    SELECT 'Thirteen '

    INSERT INTO @INNTBL_01
    SELECT 'Fourteen '

    INSERT INTO @INNTBL_01
    SELECT 'Fifteen '

    INSERT INTO @INNTBL_01
    SELECT 'Sixteen '

    INSERT INTO @INNTBL_01
    SELECT 'Seventeen '

    INSERT INTO @INNTBL_01
    SELECT 'Eighteen '

    INSERT INTO @INNTBL_01
    SELECT 'Nineteen '

    INSERT INTO @INNTBL_01
    SELECT 'Twenty '

    -- SIMILARY, INSERT THE MULTIPLES

    INSERT INTO @INNTBL_02
    SELECT 'Ten '

    INSERT INTO @INNTBL_02
    SELECT 'Twenty '

    INSERT INTO @INNTBL_02
    SELECT 'Thirty '

    INSERT INTO @INNTBL_02
    SELECT 'Forty '

    INSERT INTO @INNTBL_02
    SELECT 'Fifty '

    INSERT INTO @INNTBL_02
    SELECT 'Sixty '

    INSERT INTO @INNTBL_02
    SELECT 'Seventy '

    INSERT INTO @INNTBL_02
    SELECT 'Eighty '

    INSERT INTO @INNTBL_02
    SELECT 'Ninety '

    --STEP 3:

    ----CHECK FOR THE LIMIT OF THE AMOUNT I.E. WHAT IS THE PLACE VALUE OF DIGITS -- LACS, THOUSANDS OR HUNDREDS

    DECLARE @WORD VARCHAR(2000)

    SELECT @WORD = ''

    DECLARE @M_AMT01 BigInt,
            @M_AMT02 BigInt

    IF @AMOUNT >= 10000000
    BEGIN

        SET @M_AMT01 = @AMOUNT

        SELECT @AMOUNT = (@AMOUNT % 10000000)



        SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 10000000

        DECLARE @WORD1 VARCHAR(300)

        SET @WORD1 = ''

        IF @M_AMT01 <= 20
           AND @M_AMT01 <> 0
        BEGIN

            SET @WORD1 =
            (
                SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01
            )

        END

        SET @WORD = dbo.GetNumberToWords(@M_AMT01) + ' Crore '

    END

    IF @AMOUNT < 10000000
       AND @AMOUNT >= 100000
    BEGIN

        SET @M_AMT01 = @AMOUNT

        SELECT @AMOUNT = (@AMOUNT % 100000)

        SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 100000

        SET @WORD1 = ''

        IF @M_AMT01 < 100
           AND @M_AMT01 > 20
        BEGIN

            SET @M_AMT02 = @M_AMT01

            SET @M_AMT01 = (@M_AMT01 % 10)

            SET @M_AMT02 = (@M_AMT02 - @M_AMT01) / 10

            SET @WORD1 =
            (
                SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02
            )

        END

        IF @M_AMT01 <= 20
           AND @M_AMT01 <> 0
        BEGIN

            SET @WORD1 =
            (
                SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01
            )

        END

        SET @WORD = @WORD + @WORD1 + 'Lakh '

    END

    IF @AMOUNT < 100000
       AND @AMOUNT >= 1000
    BEGIN

        SET @M_AMT01 = @AMOUNT

        SET @AMOUNT = (@AMOUNT % 1000)

        SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 1000

        SET @WORD1 = ''

        IF @M_AMT01 < 100
           AND @M_AMT01 > 20
        BEGIN

            SET @M_AMT02 = @M_AMT01

            SET @M_AMT01 = (@M_AMT01 % 10)

            SET @M_AMT02 = (@M_AMT02 - @M_AMT01) / 10

            SET @WORD1 =
            (
                SELECT @WORD1 + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT02
            )

        END

        IF @M_AMT01 <= 20
           AND @M_AMT01 <> 0
        BEGIN

            SET @WORD1 =
            (
                SELECT @WORD1 + MTEXT + '' FROM @INNTBL_01 WHERE RECNO = @M_AMT01
            )

        END

        SET @WORD = @WORD + @WORD1 + 'Thousand '

    END

    IF @AMOUNT < 1000
       AND @AMOUNT >= 100
    BEGIN

        SET @M_AMT01 = @AMOUNT

        SET @AMOUNT = (@AMOUNT % 100)

        SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 100

        SET @WORD =
        (
            SELECT @WORD + ' ' + MTEXT + 'Hundred ' FROM @INNTBL_01 WHERE RECNO = @M_AMT01
        )

    END

    IF @AMOUNT < 100
       AND @AMOUNT > 20
    BEGIN

        SET @M_AMT01 = @AMOUNT

        SET @AMOUNT = (@AMOUNT % 10)

        SET @M_AMT01 = (@M_AMT01 - @AMOUNT) / 10

        SET @WORD =
        (
            SELECT @WORD + MTEXT + '' FROM @INNTBL_02 WHERE RECNO = @M_AMT01
        )

    END

    IF @AMOUNT <= 20
       AND @AMOUNT >= 1
    BEGIN

        SET @WORD =
        (
            SELECT @WORD + MTEXT + '' FROM @INNTBL_01 WHERE RECNO = @AMOUNT
        )

    END


    SET @WORD = @WORD + ' '

    -- STEP 4:

    -- CALCULATE THE PAISE ALSO.

    DECLARE @WORDP VARCHAR(300)

    SET @WORDP = ''

    IF @PAISE <> 0
    BEGIN

        IF @PAISE < 100
           AND @PAISE > 20
        BEGIN

            DECLARE @PAISE_01 VARCHAR(300)

            SET @PAISE_01 = @PAISE

            SET @PAISE = (@PAISE % 10)

            SET @PAISE_01 = (@PAISE_01 - @PAISE) / 10

            SET @WORDP =
            (
                SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01
            )

        END

        IF @PAISE <= 20
           AND @PAISE >= 1
        BEGIN

            SET @WORDP =
            (
                SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE
            )

        END

        SET @WORD = @WORD + 'and ' + @WORDP + 'paisa'

    END

    IF @AMOUNT >= 1000000000
    BEGIN

        SET @WORD = ''

    END


    SELECT @WORD = REPLACE(@WORD COLLATE Latin1_General_BIN, '  ', ' ');

    Return UPPER(@WORD + ' ONLY')

END
------------------------------------
Go

SELECT  dbo.GetNumericToRupees(625)

Generate Class Object From SQL Table

declare @TableName sysname = 'TABLE_Name'
declare @result Nvarchar(max) = ''

select @result = @result + '
    private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by column_id

SET @result = @result + '
'

select @result = @result + '
    public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by column_id


SELECT CONVERT(NTEXT,@result)

How To Track Database Changes

CREATE DATABASE Audit


USE [Audit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[RunDbAudit]

(
    @DataBaseName VARCHAR(100)
)
 
AS
BEGIN


DECLARE @AuditDbName VARCHAR(100);
DECLARE @BaseTable VARCHAR(100);
DECLARE @SqlCmd NVARCHAR(MAX);
DECLARE @ParamDef NVARCHAR(MAX);

SET @BaseTable =  '__DB__'+ @DataBaseName ;
SET @AuditDbName = DB_NAME();




DECLARE @AuditExcludedObjects NVARCHAR(MAX);
SET @AuditExcludedObjects = 'fn_diagramobjects,sysdiagrams,'+@BaseTable+'';

DECLARE @BaseTableCount INT;

SET @SqlCmd = 'SELECT @BaseTableCount = COUNT(*)  FROM '+@AuditDbName+'.INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_CATALOG = @AuditDbName
                 AND  TABLE_NAME = @BaseTable';
SET @ParamDef = '@BaseTable VARCHAR(100), @DataBaseName VARCHAR(100), @AuditDbName VARCHAR(100), @BaseTableCount INT OUTPUT';

                 EXECUTE sp_executesql @SqlCmd,
                 @ParamDef,
                 @BaseTable = @BaseTable, @DataBaseName = @DataBaseName, @AuditDbName = @AuditDbName, @BaseTableCount = @BaseTableCount OUTPUT;


--SELECT @SqlCmd;
--SELECT @BaseTableCount;

IF (@BaseTableCount = 0)
BEGIN

    SET @SqlCmd = '
    SELECT    
    @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , CONVERT(DECIMAL(18,2), 1.00) AS [VERSION]
    INTO [dbo].['+@BaseTable+']
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'');

    UPDATE [dbo].['+@BaseTable+'] SET [AUDIT_EXCLUDED] = 1 WHERE [OBJECT_NAME] IN (@AuditExcludedObjects)
    ';


    SET @ParamDef = '@AuditExcludedObjects NVARCHAR(MAX), @DataBaseName VARCHAR(100)';
    EXECUTE sp_executesql @SqlCmd,
    @ParamDef,
    @AuditExcludedObjects = @AuditExcludedObjects, @DataBaseName = @DataBaseName
    ;

END
ELSE
BEGIN

------------------------------------------------------------ Audit--------------------------------------------------------------

SET @SqlCmd = '

DECLARE @PrevVer DECIMAL(18,2);
DECLARE @PrevVerDt DATETIME;

DECLARE @NextVer DECIMAL(18,2);
DECLARE @NextVerDt DATETIME;

SET @NextVerDt = GETDATE();
SET @PrevVerDt = (SELECT MAX(MODIFIED_ON) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);
SET @PrevVer = (SELECT MAX([VERSION]) FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED] = 0);


SET @NextVer = @PrevVer + 0.01;

IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT

      SELECT    
      @DataBaseName AS [DB_NAME]
    , name AS [OBJECT_NAME]
    , object_id AS [OBJECT_ID]
    , type AS [OBJECT_TYPE]
    , type_desc AS [TYPE_DESC]
    , create_date AS [CREATED_ON]
    , modify_date AS [MODIFIED_ON]
    , CONVERT(DATETIME,NULL) AS [DELETED_ON]
    , CONVERT(BIT,0) AS [MASTER_TABLE]
    , CONVERT(BIT,0) AS [AUDIT_EXCLUDED]
    , CONVERT(VARCHAR(MAX),'''') AS [CHANGE_INFO]
    , @NextVer AS [VERSION]
    INTO #temp__DB_OBJECT
    FROM ['+@DataBaseName+'].sys.objects WHERE type in (''P'',''U'',''FN'',''V'')
    AND name NOT IN ((SELECT [OBJECT_NAME] FROM [dbo].['+@BaseTable+'] WHERE [AUDIT_EXCLUDED]=1))


    UPDATE P
    SET P.MODIFIED_ON = tmp.MODIFIED_ON, P.[VERSION] = tmp.[VERSION]
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN #temp__DB_OBJECT tmp ON
    (P.[OBJECT_NAME] = tmp.[OBJECT_NAME] AND P.TYPE_DESC = tmp.TYPE_DESC)
    WHERE P.AUDIT_EXCLUDED = 0
    AND P.MODIFIED_ON <> tmp.MODIFIED_ON



    INSERT INTO [dbo].['+@BaseTable+']
    SELECT t.* FROM #temp__DB_OBJECT t
    LEFT OUTER JOIN [dbo].['+@BaseTable+'] DO ON t.[OBJECT_ID] = DO.[OBJECT_ID]
    WHERE DO.[OBJECT_NAME] IS NULL



    UPDATE P
     SET P.DELETED_ON = @NextVerDt, P.[VERSION] = @NextVer
    FROM [dbo].['+@BaseTable+'] P
    INNER JOIN (
        SELECT
        DO.[OBJECT_NAME],
        DO.[OBJECT_ID],
        DO.[OBJECT_TYPE],
        DO.[TYPE_DESC]
        FROM  [dbo].['+@BaseTable+'] DO
        LEFT OUTER JOIN #temp__DB_OBJECT t ON t.[OBJECT_ID] = DO.[OBJECT_ID]
        WHERE t.[OBJECT_NAME] IS NULL AND DO.AUDIT_EXCLUDED = 0
    ) Q ON (P.[OBJECT_NAME] = Q.[OBJECT_NAME] AND P.OBJECT_TYPE = Q.OBJECT_TYPE AND P.TYPE_DESC = Q.TYPE_DESC)
    WHERE P.[DELETED_ON] IS NULL AND P.AUDIT_EXCLUDED = 0


    DECLARE @CurrentVer DECIMAL(18,2);
    SET @CurrentVer = (SELECT MAX(VERSION) FROM [dbo].['+@BaseTable+'] WHERE AUDIT_EXCLUDED = 0)

    SELECT CASE WHEN @CurrentVer = @NextVer
        THEN ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer)  + ''  :: Changes info given below.''
        ELSE ''Current Database Version : '' + CONVERT(VARCHAR,@CurrentVer) + ''  :: Changes in last version given below''
        END AS [Message];

    SELECT * FROM [dbo].['+@BaseTable+'] WHERE [VERSION] = @CurrentVer;


IF OBJECT_ID(''tempdb..#temp__DB_OBJECT'') IS NOT NULL DROP TABLE #temp__DB_OBJECT
';


SET @ParamDef = '@DataBaseName VARCHAR(100)';

EXECUTE sp_executesql @SqlCmd
,@ParamDef
,@DataBaseName = @DataBaseName;


------------------------------------------------------------/Audit--------------------------------------------------------------
END

   


END


exec RunDbAudit 'DATABASE NAME' --RUN 2 TIMES FOR 1st RUN

Convert HTML Tables To DataSet In C#

private DataSet ConvertHTMLTablesToDataSet(string HTML) {
        // Declarations
        DataSet ds = new DataSet();
        DataTable dt = null;
        DataRow dr = null;
        DataColumn dc = null;
        string TableExpression = "<table[^>]*>(.*?)</table>";
        string HeaderExpression = "<th[^>]*>(.*?)</th>";
        string RowExpression = "<tr[^>]*>(.*?)</tr>";
        string ColumnExpression = "<td[^>]*>(.*?)</td>";
        bool HeadersExist = false;
        int iCurrentColumn = 0;
        int iCurrentRow = 0;
        // Get a match for all the tables in the HTML
        MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
        // Loop through each table element
        foreach(Match Table in Tables) {
            // Reset the current row counter and the header flag
            iCurrentRow = 0;
            HeadersExist = false;
            // Add a new table to the DataSet
            dt = new DataTable();
            //Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
            // if (Table.Value.Contains("<th"))
            if (Table.Value.Contains("<th")) {
                // Set the HeadersExist flag
                HeadersExist = true;
                // Get a match for all the rows in the table
                MatchCollection Headers = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                // Loop through each header element
                foreach(Match Header in Headers) {
                    dt.Columns.Add(Header.Groups[1].ToString());
                }
            } else {
                for (int iColumns = 1; iColumns <= Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase)[0].ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++) {
                    dt.Columns.Add("Column " + iColumns);
                }
            }
            //Get a match for all the rows in the table
            MatchCollection Rows = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
            // Loop through each row element
            foreach(Match Row in Rows) {
                    // Only loop through the row if it isn't a header row
                    if (!(iCurrentRow == 0 && HeadersExist)) {
                        // Create a new row and reset the current column counter
                        dr = dt.NewRow();
                        iCurrentColumn = 0;
                        // Get a match for all the columns in the row
                        MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);
                        // Loop through each column element
                        foreach(Match Column in Columns) {
                                // Add the value to the DataRow
                                dr[iCurrentColumn] = Column.Groups[1].ToString();
                                // Increase the current column
                                iCurrentColumn++;
                            }
                            // Add the DataRow to the DataTable
                        dt.Rows.Add(dr);
                    }
                    // Increase the current row counter
                    iCurrentRow++;
                }
                // Add the DataTable to the DataSet
            ds.Tables.Add(dt);
        }
        return ds;
    }