Query to find a Table inside a Stored Procedure

Find a Stored Procedure Related to Table in Database - Search in All Stored Procedure.

Sometimes we mite need to find a table used inside a Stored Procedure

we can check for a particular table being used or not before deleting or Modifying the Structure or Changing a Primary Key etc.

—-Option 1
SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

—-Option 2
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%tablename%'

Query to find a column name inside a Database

Query to find a column name inside a Database

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EmployeeID%’
ORDER BY schema_name, table_name;

Query to change Collation of a Database

I faced a probem with a join on 2 database.
After digging for a while i found that one of my database
COLLATION was SQL_Latin1_General_CP1_CI_AS and Second was Latin1_General_CI_AI
Collation Related Error are based on Sql Server Setup or a database Restore from some external server.
As you know database can directly be restored in Sql Server 2005 without "Create new Database" and then "Restore"
Sql Server 2005
option 1. 'Latin1_General_CI_AI' (Fresh Setup on OS having no Instance of SQL Server)
option 2. 'SQL_Latin1_General_CP1_CI_AS' (Compatibility with previous versions of Sql Server.)

Query to Check for Collation
--> First Select your Database and then try this Query
PRINT 'MY DATABASE [' + DB_NAME() + '] COLLATION IS: ' + CAST( DATABASEPROPERTYEX ( DB_NAME(), N'COLLATION' ) AS VARCHAR(128) ) PRINT 'MY TEMPDB DATABASE COLLATION IS: ' + CAST( DATABASEPROPERTYEX ( 'TEMPDB', N'COLLATION' ) AS VARCHAR(128) )

Query to Change collation of A Database
ALTER DATABASE yourDBName COLLATE Latin1_General_CI_AI or SQL_Latin1_General_CP1_CI_AS or the one you need

Query all the Databases for Collation & compatibility level
SELECT NAME, COLLATION_NAME, COMPATIBILITY_LEVEL, * FROM SYS.DATABASES
SELECT SERVERPROPERTY('COLLATION')

Css for Calender Extender Control

CalenderExtender.css

.calExt_Theme1 .ajax__calendar_container
{
background-color: #e2e2e2; border:solid 1px #cccccc;

}

.calExt_Theme1 .ajax__calendar_header
{
background-color: #ffffff; margin-bottom: 4px;

}

.calExt_Theme1 .ajax__calendar_title,

.calExt_Theme1 .ajax__calendar_next,

.calExt_Theme1 .ajax__calendar_prev

{
color: #004080; padding-top: 3px;

}

.calExt_Theme1 .ajax__calendar_body

{
background-color: #e9e9e9; border: solid 1px #cccccc;

}

.calExt_Theme1 .ajax__calendar_dayname

{
text-align:center; font-weight:bold; margin-bottom: 4px; margin-top: 2px;

}


.calExt_Theme1 .ajax__calendar_day

{
text-align:center;

}

.calExt_Theme1 .ajax__calendar_hover .ajax__calendar_day,

.calExt_Theme1 .ajax__calendar_hover .ajax__calendar_month,

.calExt_Theme1 .ajax__calendar_hover .ajax__calendar_year,

.calExt_Theme1 .ajax__calendar_active

{
color: #004080; font-weight:bold; background-color: #ffffff;

}

.calExt_Theme1 .ajax__calendar_today

{
font-weight:bold;

}

.calExt_Theme1 .ajax__calendar_other,

.calExt_Theme1 .ajax__calendar_hover .ajax__calendar_today,

.calExt_Theme1 .ajax__calendar_hover .ajax__calendar_title

{
color: #bbbbbb;

}


Handling Javascript KeyPress

There are several reasons why you may want to capture the key press event in a browser window.
Getting the pressed key is easy in JavaScript, however different browsers use different ways for this. We will first see how this can on both Internet Explorer and Firefox.

Thanks to onkeyup we can easily handle the key pressing event in Internet Explorer. Here is the script we need to capture some of the keys on the keyboard:



<script type="text/javascript">

document.onkeyup = KeyCheck;


function KeyCheck(e)

{

var KeyID = (window.event) ? event.keyCode : e.keyCode;


switch(KeyID)

{

case 16:

document.Form1.KeyName.value = "Shift";

break;

case 17:

document.Form1.KeyName.value = "Ctrl";

break;

case 18:

document.Form1.KeyName.value = "Alt";

break;

case 19:

document.Form1.KeyName.value = "Pause";

break;

case 37:

document.Form1.KeyName.value = "Arrow Left";

break;

case 38:

document.Form1.KeyName.value = "Arrow Up";

break;

case 39:

document.Form1.KeyName.value = "Arrow Right";

break;

case 40:

document.Form1.KeyName.value = "Arrow Down";

break;
}

}
</script>


And the HTML to use with this is:

<form name="Form1">

<input type="text" name="KeyName" value="" />
</form>

Check username with Ajax using PageMethod

Ajax PageMethod

MS AJAX has gone one step further! We can now call methods in the codebehind of the current page from Javascript. Here's how:

Here i am tryin to Check whether the USERNAME Exists in my database.

----- Default.aspx

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="True" />
<asp:TextBox ID="TxtEventCode" runat="server" CssClass="StyleTextForm"
onblur="usernameChecker(this.value);"></asp:TextBox>
<span id ="spanAvailability"></span>

JavaScript part with the Script tag.

<script type="text/javascript">
var usernameCheckerTimer;
var spanAvailability = $get("spanAvailability");

function usernameChecker(username) {
clearTimeout(usernameCheckerTimer);
if (username == "")
//if (username.length == 0)
spanAvailability.innerHTML = "Code cannot be Blank";
else {
spanAvailability.innerHTML = "checking...";
usernameCheckerTimer = setTimeout("checkUsernameUsage('" + username + "');", 750);
}
}

function checkUsernameUsage(username) {
// initiate the ajax pagemethod call
// upon completion, the OnSucceded callback will be executed
PageMethods.IsUserAvailable(username, OnSucceeded);
}

// Callback function invoked on successful completion of the page method.
function OnSucceeded(result, userContext, methodName) {
if (methodName == "IsUserAvailable") {
if (result == true)
//spanAvailability.innerHTML = "Available";
spanAvailability.innerHTML = "Already Exists";
else
spanAvailability.innerHTML = "Does Not Exist";
}
}
</script>

***** Do not forget to set the EnablePageMethods attribute to true. ******

------- Default.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

namespace CheckUsernameWithAJAX
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}


[WebMethod]
public static bool IsUserAvailable(string username)
{
SqlConnection cnn = new SqlConnection("data source=SYSTEMNAME; initial catalog=EVENTMGM; user ID=xxxx; Password=xxxx");
string Sqlstr;
//SqlCommand cmd = new SqlCommand();
//cmd.Connection= cnn;
//cmd.CommandType = CommandType.Text;
Sqlstr="SELECT USERNAME FROM USERLOG WHERE CODE = '" + username + "' ";
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, cnn);
DataSet ds = new DataSet();
da.Fill(ds, "USERLOG");
//cmd.CommandText= Sqlstr;
//cnn.Open();
//int cnt = cmd.ExecuteNonQuery();

//cnn.Close();

if ( ds.Tables["HOTELMAS"].Rows.Count > 0)
return true;
else
return false;

//if (username.ToLower() == "travis")
// return false;
//else
// return true;
}
}
}

------- Default.aspx.vb
Imports System.Web.Services
Partial Class ClassName
<WebMethod()> _
Public Shared Function IsUserAvailable(ByVal username As String) As Boolean
Dim Strconn As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString
Dim Sqlstr As String
Dim cnn As SqlConnection = New SqlConnection(Strconn)
Sqlstr = "SELECT USERNAME FROM USERLOG WHERE CODE = '" & username & "'"
Dim da As SqlDataAdapter = New SqlDataAdapter(Sqlstr, cnn)
Dim ds As New DataSet
da.Fill(ds, "EVE")
If ds.Tables("EVE").Rows.Count > 0 Then
Return True
Else

Return False
End If
End Function
End Class

Mark your method as static and give it the WebMethod attribute

The method has to be declared static. It must also be marked with the WebMethod attribute. You'll probably find that you need to include System.Web.Services

Ajax AutoComplete Extender

This sample demonstrate, how you can use AutoCompleteExtender control from ASP.NET AJAX Control Toolkit to fetch more than one column & after Selecting a particular value inside ACE, it displays other columns data Respectively.
we need four things, one would be our
sample contact page, a webservice, & two class files CodeManager.vb & Hotel.vb.

--------------Scripting here
Default.aspx file
Here u need Drag & Drop a Script Manager

<asp:TextBox ID="TxtCode" runat="server" MaxLength="10"
CssClass="StyleTextForm" Width="123px" ></asp:TextBox>
<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TxtCode"
ServicePath="~/WebService.asmx"
ServiceMethod="GetName" MinimumPrefixLength="1"
OnClientItemSelected="OnContactSelected"
CompletionListCssClass="StyleTextForm" >
</cc1:AutoCompleteExtender>


Javascript
function OnContactSelected(source, eventArgs) {
var results = eval('(' + eventArgs.get_value() + ')');
// $get('TxtCode').value = results.Code;
// $get('LblName').value = results.Name;
$get('ctl00_ContentPlaceHolder1_TxtCode').value = results.Code;
$get('ctl00_ContentPlaceHolder1_TxtName').value = results.Name;
}

since i have used it in my MasterPage and Contentpage i have to use
$get('ctl00_ContentPlaceHolder1_TxtName').value
else we can directly use it
$get('TxtCode').value
-----------------------------------

Here is our webservice code.

Code for Webservice.vb
-------------

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections
Imports System.Web.Script.Services

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class WebService
Inherits System.Web.Services.WebService

Dim Strconn As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString

<WebMethod(), System.Web.Script.Services.ScriptMethod()>_
Public Function GetName(ByVal prefixText As String, ByVal count As Integer) As String()
Dim items As New List(Of String)()
Dim Serializer As New JavaScriptSerializer
Dim manager As New CodeManager
Dim Codes As List(Of Hotel) = manager.GetCodes(prefixText, count)
For Each c As Hotel In Codes
items.Add(AutoCompleteExtender.CreateAutoCompleteItem(c.Code, Serializer.Serialize(c)))
Next
Return items.ToArray()
End Function

End Class


Code for class file CodeManager.vb
-------------------------------------

Imports System.Data
Imports System.Data.SqlClient
'Imports System.Collections.Generic

Public Class CodeManager
Dim Strconn As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString
Protected dsHotels As DataSet

Public Sub New()
dsHotels = New DataSet
End Sub

Public Function GetCodes(ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
'Public Function GetCodes(ByVal id As Integer, ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
Dim items As New List(Of Hotel)()
Dim cnn As SqlConnection = New SqlConnection(Strconn)
Dim Sqlstr = "SELECT TOP({0}) CODE, [NAME] FROM HOTELMAS WHERE (CODE LIKE '{1}%')"
Dim adapter As New SqlDataAdapter(String.Format(Sqlstr, count, prefix), cnn)
adapter.Fill(dsHotels, "Hotels")
For Each row As DataRow In dsHotels.Tables("Hotels").Rows
Dim Item As New Hotel
Item.Code = DirectCast(row("CODE").ToString.Trim, String)
Item.Name = DirectCast(row("NAME").ToString.Trim, String)
items.Add(Item)
Next
Return items
End Function
End Class

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

code for Hotel.vb Class
--------------------------

Public Class Hotel
Private _Code As String
Public Property Code() As String
Get
Return _Code
End Get
Set(ByVal value As String)
_Code = value
End Set
End Property

Private _Name As String
Public Property Name() As String
Get
Return _Name
End Get
Set(ByVal value As String)
_Name = value
End Set
End Property
End Class

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

Code in C#

code for WebService.cs
------------------

using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using System.Web.Services;
using System.Web.Services.Protocols;
using AjaxControlToolkit;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService()]
public class WebService : System.Web.Services.WebService
{

[WebMethod(), System.Web.Script.Services.ScriptMethod()]
public string[] GetName(string prefixText, int count)
{
List items = new List();
JavaScriptSerializer Serializer = new JavaScriptSerializer();
CodeManager manager = new CodeManager();
List Codes = manager.GetCodes(prefixText, count);
foreach (Hotel c in Codes) {
items.Add(AutoCompleteExtender.CreateAutoCompleteItem(c.Code, Serializer.Serialize(c)));
}
return items.ToArray();
}
}

--------------------
Coder for class file CodeManager.cs
-----------------------

using System.Data;
using System.Data.SqlClient;
//Imports System.Collections.Generic

public class CodeManager
{
string Strconn = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString;
protected DataSet dsHotels;

public CodeManager()
{
dsHotels = new DataSet();
}

public List GetCodes(string prefix, int count)
{
//Public Function GetCodes(ByVal id As Integer, ByVal prefix As String, ByVal count As Integer) As List(Of Hotel)
List items = new List();
SqlConnection cnn = new SqlConnection(Strconn);
var Sqlstr = "SELECT TOP({0}) CODE, [NAME] FROM HOTELMAS WHERE (CODE LIKE '{1}%')";
SqlDataAdapter adapter = new SqlDataAdapter(string.Format(Sqlstr, count, prefix), cnn);
adapter.Fill(dsHotels, "Hotels");
foreach (DataRow row in dsHotels.Tables("Hotels").Rows) {
Hotel Item = new Hotel();
Item.Code = (string)row("CODE").ToString.Trim;
Item.Name = (string)row("NAME").ToString.Trim;
items.Add(Item);
}
return items;
}
}

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

code for Hotel.cs Class
--------------------------

using System.Data;
using System.Data.SqlClient;
using System.Web.UI.HtmlControls;

public class Hotel
{
private string _Code;
public string Code {
get { return _Code; }
set { _Code = value; }
}

private string _Name;
public string Name {
get { return _Name; }
set { _Name = value; }
}
}

-------------------------------------------
Thats it ur Ready for Ajaxing !!!
My first attempt on ajax auto extensions
Hope u enjoy this example