September 2009

Generate Storeprocedure Dynamically

sql
SqlConnection conn;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{

}
}
protected void btnConnect_Click(object sender, EventArgs e)
{
if (txtUserID.Text == “” && txtPassword.Text == “”)
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();
BindTable();
BindProcedure();
}
else
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; user id=” + txtUserID.Text + “;password=” + txtPassword.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();
BindTable();
BindProcedure();
}
}
public void BindTable()
{
string str = “select Table_name from information_schema.tables”;
SqlDataAdapter adp = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
ddlTable.DataSource = ds;
ddlTable.DataTextField = “table_name”;
ddlTable.DataValueField = “table_name”;
ddlTable.DataBind();
}

public void BindProcedure()
{
string str = “select name from sys.procedures where object_definition(object_id) not like ‘%sp%'”;
SqlDataAdapter adp = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
ddlProcedure.DataSource = ds;
ddlProcedure.DataTextField = “name”;
ddlProcedure.DataValueField = “name”;
ddlProcedure.DataBind();
}

It will genrate insert store procedure, write below script on Generate button OnClick event
protected void btnGenerate_Click(object sender, EventArgs e)
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; user id=” + txtUserID.Text + “;password=” + txtPassword.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();

string strTbl = “select column_name, data_type, character_maximum_length from information_schema.columns where columnproperty(object_id(table_name),column_name, ‘IsIdentity’)=0 and table_name='” + ddlTable.SelectedValue.ToString() + “‘”;

SqlDataAdapter adp = new SqlDataAdapter(strTbl, conn);
SqlCommand cmd1 = new SqlCommand(strTbl, conn);
SqlDataReader rdr;

rdr = cmd1.ExecuteReader();
DataTable schemaTable = rdr.GetSchemaTable();

string strPro;
ArrayList pra = new ArrayList();
ArrayList pram = new ArrayList();
string pp;

while (rdr.Read())
{
if (rdr[1].ToString() == “int”)
{
pp = “@” + rdr[0].ToString() + ” ” + “int”;
pra.Add(pp);
pram.Add(“@” + rdr[0].ToString());
}
else
{
pp = “@” + rdr[0].ToString() + ” ” + rdr[1].ToString() + “(” + rdr[2].ToString() + “)”;
pra.Add(pp);
pram.Add(“@” + rdr[0].ToString());
}
}

rdr.Close();

string ch = ddlGenerate.SelectedValue.ToString();
if (ch == “Insert Procedure”)
{
strPro = (“Create procedure ins” + ddlTable.SelectedValue.ToString() + ” (“);
for (int j = 0; j < pra.Count; j++)
{
strPro += (pra[j]);
if (j == pra.Count – 1)
{
}
else
{
strPro+=”,”;
}
}
strPro+=”) As “;
strPro +=”Insert into ” + ddlTable.SelectedValue.ToString() + ” values (“;
for (int j = 0; j < pra.Count; j++)
{
strPro += (pram[j]);
if (j == pram.Count – 1)
{
}
else
{
strPro += “,”;
}
}
strPro +=”)”;

Response.Write(strPro);
SqlCommand cmd = new SqlCo

Check IsIdentity true or false

Find SQL Table Cloumn name, data type,  column size

select column_name, data_type, character_maximum_length char from information_schema.columns where table_name=’tblCountry’

Find IsIdentity true columns

SELECT * from information_schema.columns where columnproperty(object_id(table_name),column_name, ‘IsIdentity’)=1
and table_name=’personal’

Find IsIdentity False Columns

SELECT * from information_schema.columns where columnproperty(object_id(table_name),column_name, ‘IsIdentity’)=0
and table_name=’personal’

Find Database, Tables, Procedure Names in SQL Server

Find all database names in sql server

select name from sys.databases

Find all tables names in sql server for perticular database

select Table_name from information_schema.tables

Find all procedure names in sql server for perticular database

select name from sys.procedures where object_definition(object_id) not like ‘%sp%’

SELECT name FROM sys.objects WHERE type = ‘P’