Drop All User-Defined Types from SQL Server database at a time

//////  Query

Select  'DROP TYPE ' + quotename(schema_name(schema_id)) + '.' + quotename(name) from sys.types

Where is_user_defined = 1

// // REsult

DROP TYPE [dbo].[Type_tblHH]

DROP TYPE [dbo].[Type_tblHH_ConsumerGoods]

DROP TYPE [dbo].[Type_tblHH_Crops]

DROP TYPE [dbo].[Type_tblHH_Deceased]

DROP TYPE [dbo].[Type_tblHH_DrinkingWater]

run execute command and get result

Drop All Tables from database using sql query at a time

///   Query

SELECT 'DROP Table [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'

FROM INFORMATION_SCHEMA.TABLES

///   Result

DROP Table [DBName][dbo].[HH_Get_Sec_91]

DROP Table [DBName][dbo].[HH_Get_Sec_92]

DROP Table [DBName][dbo].[HH_Get_Sec_9]

DROP Table [DBName][dbo].[HH_Get_Sec_8]

DROP Table [DBName][dbo].[HH_Get_Sec_7_Social]

Run execute command and get result

Drop All procedures from database using sql query at a time

/// Query

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'

FROM sys.procedures p

/// REsult

DROP PROCEDURE [dbo].[HH_Get_Sec_91]

DROP PROCEDURE [dbo].[HH_Get_Sec_92]

DROP PROCEDURE [dbo].[HH_Get_Sec_9]

DROP PROCEDURE [dbo].[HH_Get_Sec_8]

DROP PROCEDURE [dbo].[HH_Get_Sec_7_Social]

// now execute command and drop all procedure related to the DB

how to get dbname, servername, user, password etc from connection string using asp.net c#

//    Connection string

<add name="DBConnection" providerName="System.Data.SqlClient" connectionString="server=127.0.0.1;database=DBName;user=UserID;password=Pwd/>

//     Method 1-

public static string MainDBName()

    {

        string conName = "";

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ToString()))

        {

          conName=  conn.Database.ToString();

          conName = conn.DataSource.ToString();

        }

        return conName;

    }

//     Method 2-

public static SqlConnectionStringBuilder SSB = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["DBConnection"].ToString());

    public static string server = Convert.ToString(SSB["server"]);

    public static string database = Convert.ToString(SSB["database"]);

    public static string UserID = Convert.ToString(SSB["user"]);

    public static string password = Convert.ToString(SSB["password"]);

Comma Separate string return comma separated value in sql server from table using function

// Create function

create FUNCTION [dbo].[CommanTable_CommaSaparated_FromSaparated](@StrClm VARCHAR(MAX),@Flag int)

RETURNS VARCHAR(MAX)

BEGIN

DECLARE @ListStr VARCHAR(MAX),@Ids varchar(50) = ''

set @Ids=','+@StrClm+','

SELECT @ListStr = COALESCE(@ListStr+',' ,'') + Value FROM tblMstCommon where Charindex(','+cast(ID as varchar(8000))+',', @Ids) > 0 and Flag=@Flag

RETURN @ListStr

END

// Call function 

select dbo.CommanTable_CommaSaparated_FromSaparated('1,2,3,',525)as Result

// REsult

Converting Comma Separated Value in to Rows in SQL Server function

// Sql query

CREATE FUNCTION Fun_BreakStringIntoRows (@CommadelimitedString   varchar(1000))

RETURNS   @Result TABLE (Column1   VARCHAR(100))

AS

BEGIN

        DECLARE @IntLocation INT

        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)

        BEGIN

              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)     

              INSERT INTO   @Result (Column1)

              --LTRIM and RTRIM to ensure blank spaces are   removed

              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))  

              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '')

        END

        INSERT INTO   @Result (Column1)

        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed

        RETURN

END

GO

// Execute  Sql query

select * from Fun_BreakStringIntoRows('1, 2, 3') where BreakStringIntoRows.Column1=1

//  Result  

Get Name of days between tow dates in sql server

//           Create function

Create FUNCTION [dbo].[Get_NameOf_Days](

@startdate datetime

,@enddate datetime

,@DayValue int -- return the value of days (starting day with sunday- value=1)

,@NameValue int -- return the name of days (starting day with monday- value=0)

)

RETURNS VARCHAR(MAX)

BEGIN

declare @count int =0

    while @startdate<=@enddate

    Begin

        IF DatePart(WEEKDAY,@startdate) = @DayValue

            SET @count=@count+1

        SET @startdate=DateAdd(d,1,@startdate)

    END

RETURN  (select DATENAME(dw,@NameValue)+' : '+CAST(@count as varchar))

END

//           Call  function

select dbo.Get_NameOf_Days('2017-1-1','2017-1-31',1,6)

//           Result