All tables name like

ome time we need to get all the tables which start with any character.We have imported data form excel sheet to MS SQL database. Now want to get list of table and delete the imported data table from database.

This query will helpful to get the table from database

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%$%'

Drop all db objects

Some time we need to restore the database with the help of MS SQL server generate script.
We need to flow these Steps

1. Generate Database Scripts(Schema and data)
2. Drop all db objects
3. Restore Generated script   

Drop all db objects

This below script user can use to drop all database objects from database and

1. Store procedures
2. Views
3. Functions
4. Tables

DB Utility script

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Get days, months, and years between dates in SQL

Example:-

Select Command:-

SELECT * FROM dbo.GetDateDifferenceInYearsMonthsDays('2019-04-27 10:24:55.387',GETDATE())

Create SQL Function:-

CREATE FUNCTION [dbo].[GetDateDifferenceInYearsMonthsDays]
(
    @FromDate DATETIME, @ToDate DATETIME
)
RETURNS
 @DateDifference TABLE (
 YEAR INT,  MONTH INT, DAYS INT)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END)
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)
   
    INSERT INTO @DateDifference
    VALUES(@Years, @Months, @Days)
   
    RETURN
END

Find and Remove Duplicate Rows in a Table using SQL Server

Syntax:-

--//synatx to find duplicate and count the no of rows
;WITH CTE AS
(
    SELECT ColomnName, ROW_NUMBER() OVER
    (
        PARTITION BY ColomnName ORDER BY ID
    ) RowNumber
    FROM  TableName
)

--// to check duplicate record select command
SELECT *   FROM CTE WHERE RowNumber > 1

--//Delete duplicate  record
DELETE FROM CTE WHERE RowNumber > 1


Example:-

i have the table that contain the duplicate sector

Syntax to select the duplicate record:-

;WITH CTE AS
(
    SELECT name, ROW_NUMBER() OVER
    (
        PARTITION BY name ORDER BY id
    ) RowNumber
    FROM  dbo.Sectors
)


SELECT *   FROM CTE WHERE RowNumber > 1

Syntax to delete the duplicate Data:-

;WITH CTE AS
(
    SELECT name, ROW_NUMBER() OVER
    (
        PARTITION BY name ORDER BY id
    ) RowNumber
    FROM  dbo.Sectors
)
DELETE  FROM CTE WHERE RowNumber > 1

Convert multiple rows into one with comma as separator

Introduction:-

in this article we will learn how to convert multiple row into one comma separator.

Example:-



I have table that contain the skill data . now convert it comma separated form.

Synatx:-

1. 

Declare @tmp varchar(250)

SET @tmp = ''

select @tmp = @tmp + ColomnName + ', ' from TableName

select SUBSTRING(@tmp, 0, LEN(@tmp))

2.

select

stuff((

SELECT ',' + ColomnName 

FROM     TableName

for xml path('')

1.      ),1,1,'') as name_csv

Adding multiple class using ng-class in Angularjs

Introduction:- in this article we will learn how to apply multiple class in angularJs.


Definition:- we can apply classes multiple way some of them in this way.

1. ng-class using array syntax:

ng-class="{expression1:'class1',expressionData2:'class2'}[Status]"

2. Adds 'odd' or 'even' classes to div

ng-class="[{0:'even', 1:'odd'}[ $index % 2]]"

3. ternary operator notation:

 ng-class="expression1? 'class1 class2' : 'class3 class4'"

4. we have multiple expression to add multiple ng-class.

 ng-class="{class1: expressionData1, class2: expressionData2}"

 ng-class="{class1 : expression1, class2 : expression2}">
    Hello World!

How to Use Table In ASP.NET

Example:-

ASP.Net Code:-

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body style="padding:25px">

    <form id="form1" runat="server">

    <div>

        <h2 style="color:MidnightBlue; font-style:italic;">     

            How to use Table in asp.net

        </h2>     

        <hr width="450" align="left" color="Gainsboro" />

        <asp:Table ID="Table1"

            runat="server"

            Font-Size="X-Large"

            Width="550"

            Font-Names="Palatino"

            BackColor="Orange"

            BorderColor="DarkRed"

            BorderWidth="2"

            ForeColor="Snow"

            CellPadding="5"

            CellSpacing="5"

            >

            <asp:TableHeaderRow

                runat="server"

                ForeColor="Snow"

                BackColor="OliveDrab"

                Font-Bold="true"

                >

                <asp:TableHeaderCell>Serial</asp:TableHeaderCell>

                <asp:TableHeaderCell>Name</asp:TableHeaderCell>

                <asp:TableHeaderCell>Value</asp:TableHeaderCell>

            </asp:TableHeaderRow>

            <asp:TableRow

                ID="TableRow1"

                runat="server"

                BackColor="OrangeRed"

                >

                <asp:TableCell>1</asp:TableCell>

                <asp:TableCell>Azure</asp:TableCell>

                <asp:TableCell>#F0FFFF</asp:TableCell>

            </asp:TableRow>

            <asp:TableRow

                ID="TableRow2"

                runat="server"

                BackColor="DarkOrange"

                >

                <asp:TableCell>2</asp:TableCell>

                <asp:TableCell>Beige</asp:TableCell>

                <asp:TableCell>#F5F5DC</asp:TableCell>

            </asp:TableRow>

            <asp:TableRow

                ID="TableRow3"

                runat="server"

                BackColor="OrangeRed"

                >

                <asp:TableCell>3</asp:TableCell>

                <asp:TableCell>Bisque</asp:TableCell>

                <asp:TableCell>#FFE4C4</asp:TableCell>

            </asp:TableRow>

            <asp:TableRow

                ID="TableRow4"

                runat="server"

                BackColor="DarkOrange"

                >

                <asp:TableCell>4</asp:TableCell>

                <asp:TableCell>Crimson</asp:TableCell>

                <asp:TableCell>#DC143C</asp:TableCell>

            </asp:TableRow>

            <asp:TableRow

                ID="TableRow5"

                runat="server"

                BackColor="OrangeRed"

                >

                <asp:TableCell>5</asp:TableCell>

                <asp:TableCell>Cyan</asp:TableCell>

                <asp:TableCell>#00FFFF</asp:TableCell>

            </asp:TableRow>

            <asp:TableFooterRow

                runat="server"

                BackColor="DarkOrange"

                >

                <asp:TableCell

                    ColumnSpan="3"

                    HorizontalAlign="Right"

                    Font-Italic="true"

                    >

                    Number of colors 5

                </asp:TableCell>

            </asp:TableFooterRow>

        </asp:Table>

    </div>

    </form>

</body>

</html>