How to find a particular table or any keyword from all stored procedures of database

We can find it by simple SQL query: -

SELECT name,create_date, modify_date, type

FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Table_Name_Or_Any_Keyword%'

How to get alert message before redirect a page

In this article, I will explain how to display JavaScript alert message box and the redirect to another page or website when "OK" Button is clicked.

ScriptManager.RegisterStartupScript(this, GetType(), "alertMessage", "alert('User details saved sucessfully');window.location = 'anotherpage.aspx'; ", true);

Find all tables containing particular column

 

In this article, I am explaining how to find all tables containing a particular column of SQL statements.

Search Tables:

SELECT c.name  AS 'ColumnName' ,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables  t   ON c.object_id = t.object_id
WHERE c.name LIKE '%my_column_name%'
ORDER BY TableName,ColumnName

Search Tables & Views:

SELECT  COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS  'TableName'
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE  COLUMN_NAME LIKE '%MyName%'
ORDER BY TableName, ColumnName

Get Response using HttpWebRequest class with Json request and Basic Authorization

In this article,  I am explaining, How to get a response using HttpWebRequest Class.

Below is Sample Sode

 private string GetResponse()

  {

    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

    string sCompleteRequest = apiUrl + "/payments/v1/sessions";

    HttpWebRequest oHttpWebRequest = WebRequest.Create(sCompleteRequest) as HttpWebRequest;

    oHttpWebRequest.Method = "POST";

    oHttpWebRequest.ContentType = "application/json";

    string sCredential = "username:password";//pass credential here

   

    byte[] bData = System.Text.Encoding.UTF8.GetBytes(sCredential);

    string sAuthorization = Convert.ToBase64String(bData);

 

    oHttpWebRequest.Headers.Add("Authorization", "Basic " + sAuthorization);

    Byte[] oRequestByte = System.Text.Encoding.UTF8.GetBytes(GetStringJson().ToString());

    Stream oRequestStream = oHttpWebRequest.GetRequestStream();

    oRequestStream.Write(oRequestByte, 0, oRequestByte.Length);

    oRequestStream.Close();

 

    string sCompleteResponse = string.Empty;

    using (HttpWebResponse oHttpWebResponse = oHttpWebRequest.GetResponse() as HttpWebResponse)

    {

      if (oHttpWebResponse.StatusCode == HttpStatusCode.OK)

      {

        Stream oStream = oHttpWebResponse.GetResponseStream();

        StreamReader oStreamReader = new StreamReader(oStream);

        sCompleteResponse = oStreamReader.ReadToEnd();

      }

    }

    // Get values from response

    JObject oJObject = JObject.Parse(sCompleteResponse);

    JToken oJToken = oJObject["order"];

 

    int iOrderId = Numericcl.GetIntValue(oJToken["id"]);

    string sStatus = Stringcl.GetValue(oJToken["status"]);

    string sOldStatus = Stringcl.GetValue(oJToken["old_status"]);

    string sDescription = Stringcl.GetValue(oJToken["description"]);

 

    // another way  Get values from response

    m_sClientToken = oJObject.SelectToken("client_token").Value<string>();

 

    iOrderId = oJObject.SelectToken("order.id").Value<int>();

    decimal dDayTemp = oJObject.SelectToken("list[0].temp.day").Value<decimal>();

 

    return sCompleteResponse;

  } 

Simple C# .NET 4.5 HTTPClient Request Using Basic Auth and Proxy

 In this article,  I am explaining, How to get response using HttpClient Class.

HttpClient Class

Namespace:System.Net.Http

Assembly:System.Net.Http.dll

using System;

using System.Text;

using System.Threading.Tasks;

using System.Net.Http;

using System.Net;

using System.Web.Script.Serialization;

 

namespace HTTP_Test

{

  class program

  {

    static void Main()

    {

      Task t = new Task(HTTP_GET);

      t.Start();

      Console.ReadLine();

    }

 

    static async void HTTP_GET()

    {

      var TARGETURL = "http://en.wikipedia.org/";

 

      HttpClientHandler handler = new HttpClientHandler()

      {

        Proxy = new WebProxy("http://127.0.0.1:8888"),

        UseProxy = true,

      };

 

      Console.WriteLine("GET: + " + TARGETURL);

      // ... Use HttpClient.           

      HttpClient client = new HttpClient(handler);

 

      var byteArray = Encoding.ASCII.GetBytes("username:password1234");

      client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));

 

      HttpResponseMessage response = await client.GetAsync(TARGETURL);

      HttpContent content = response.Content;

 

      // ... Check Status Code                               

      Console.WriteLine("Response StatusCode: " + (int)response.StatusCode);

 

      // ... Read the string.

      string result = await content.ReadAsStringAsync();

 

      // ... Display the result.

      if (result != null && result.Length >= 50)

      {

        Console.WriteLine(result.Substring(0, 50) + "...");

      }

    }

 

    static void GetResponseJson(string sJsonString)

    {

      string inputJson = (new JavaScriptSerializer()).Serialize(sJsonString);

      HttpClient client = new HttpClient();

      HttpContent inputContent = new StringContent(inputJson, Encoding.UTF8, "application/json");

      var byteArray = Encoding.ASCII.GetBytes("username:password1234");

      client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));

 

      HttpResponseMessage response = client.PostAsync("https://testing.com" + "/GetClient", inputContent).Result;

    }

  }

}

Ref- https://gist.github.com/bryanbarnard/8102915

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

 In this article, I am explaining how to Drop all the tables, stored procedures, triggers, constraints, and all dependencies in one SQL statement.

/* 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

 Ref-https://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend