How to search all columns of all tables in a database for a keyword

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables

(

               @SearchStr nvarchar(100)

)

AS

BEGIN

               -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

               -- Purpose: To search all columns of all tables for a given search string

               -- Written by: Narayana Vyas Kondreddi

               -- Site: http://vyaskn.tripod.com

               -- Tested on: SQL Server 7.0 and SQL Server 2000

               -- Date modified: 28th July 2002 22:50 GMT

               CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

               SET NOCOUNT ON

               DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

               SET  @TableName = ''

               SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

               WHILE @TableName IS NOT NULL

               BEGIN

                               SET @ColumnName = ''

                               SET @TableName =

                               (

                                              SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

                                              FROM        INFORMATION_SCHEMA.TABLES

                                              WHERE                     TABLE_TYPE = 'BASE TABLE'

                                                             AND          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

                                                             AND          OBJECTPROPERTY(

                                                                                            OBJECT_ID(

                                                                                                           QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                                                                                                            ), 'IsMSShipped'

                                                                                                   ) = 0

                               )

                               WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

                               BEGIN

                                              SET @ColumnName =

                                              (

                                                             SELECT MIN(QUOTENAME(COLUMN_NAME))

                                                             FROM        INFORMATION_SCHEMA.COLUMNS

                                                             WHERE                     TABLE_SCHEMA           = PARSENAME(@TableName, 2)

                                                                            AND          TABLE_NAME              = PARSENAME(@TableName, 1)

                                                                            AND          DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                                                                            AND          QUOTENAME(COLUMN_NAME) > @ColumnName

                                              )

              

                                              IF @ColumnName IS NOT NULL

                                              BEGIN

                                                             INSERT INTO #Results

                                                             EXEC

                                                             (

                                                                            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

                                                                            FROM ' + @TableName + ' (NOLOCK) ' +

                                                                            ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                                                             )

                                              END

                               END         

               END

               SELECT ColumnName, ColumnValue FROM #Results

END

Creating javascript alerts in ASP.NET with UpdatePanel

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UpdatePanel.aspx.cs" Inherits="UpdatePanel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

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

<head runat="server">

  <title>Untitled Page</title>

</head>

<body>

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

      <asp:ScriptManager ID="scriptMangerTest" runat="Server">

      </asp:ScriptManager>

      <asp:UpdatePanel ID="updateTest" runat="Server">

          <ContentTemplate>

              <div>

                  <table cellpadding="0" cellspacing="0" width="60%">

                      <tr>

                          <td align="right">

                              <asp:Label ID="lblName" runat="Server" Text="Enter E-mail Id">

          

                              </asp:Label>

                          </td>

                          <td>

                              <asp:TextBox ID="txtEmail" runat="Server"></asp:TextBox>

                          </td>

                      </tr>

                      <tr>

                          <td colspan="2" align="left">

                              <asp:Button ID="btnCheck" runat="Server" Text="Validate" OnClick="btnCheck_Click" />

                          </td>

                      </tr>

                  </table>

              </div>

          </ContentTemplate>

      </asp:UpdatePanel>

  </form>

</body>

</html>

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

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class UpdatePanel : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void btnCheck_Click(object sender, EventArgs e)

    {

        if (!CheckEmail(txtEmail.Text))

        {

            Guid gMessage = Guid.NewGuid();

            string sMessage = "alert('Invalid E-mail Id ');";

            ScriptManager.RegisterStartupScript(updateTest, updateTest.GetType(), gMessage.ToString(), sMessage, true);

        }

    }

    private bool CheckEmail(string EmailAddress)

    {

        string strPattern = @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$";

        if (System.Text.RegularExpressions.Regex.IsMatch(EmailAddress, strPattern))

        { return true; }

        return false;

    }

}

LINQ Insert Update Delete

ASPX.CS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

        Class1 obj = new Class1();

        obj.insert(txtname.Text, txtaddress.Text);

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        Class1 obj = new Class1();

        obj.delete(Convert.ToInt32(txtid.Text));

    }

}

Class file

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Linq;

using System.Web;

using System.Configuration;

/// <summary>

/// Summary description for Class1

/// </summary>

public class Class1

{

    public string aaa = ConfigurationManager.ConnectionStrings["nareshConnectionString"].ToString();

    DataClassesDataContext aaaa = new DataClassesDataContext();

    public Class1()

    {

        //

        // TODO: Add constructor logic here

        //

    }

    public void insert(string name, string address)

    {

        T_Linq obj = new T_Linq();

        //obj.id = id;

        obj.name = name;

        obj.address = address;

        aaaa.T_Linqs.InsertOnSubmit(obj);

        aaaa.SubmitChanges();

    }

    public void delete(int id)

    {

        T_Linq objid = (from p in aaaa.T_Linqs

                          where p.id == id

                          select p).SingleOrDefault();

        objid.id = id;

        aaaa.T_Linqs.DeleteOnSubmit(objid);

        aaaa.SubmitChanges();

    }

}