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