Get foreign key of a table in SQL

By the following query you will get the foreign key and primary key relationship between all the tables in database.
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

 

Get Primary key of a table in SQL

By the following query you will get column name of table in current database.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = ‘Table_Name’

 

Passing column name as argument to procedure in SQL

By the following way you can pass the column name as a parameter/argument to the procedure in SQL.

create proc PROCEDURENAME

as

begin

declare @CoumnName varchar(30),@Query varchar(100);

set @ColumnName=’ColumnName’;

set @Query=’select FoodName,ImageUrl,Price from tblFood where ‘+@ColumnName+’=condition’

exec sp_executesql @Query

end

 

Read only Database

To make a database as read only,

ALTER DATABASE [Database name] SET READ_ONLY

To remove read only property of database,

ALTER DATABASE [Database name] SET READ_WRITE

 

Procedure to Insert EmployeeId Automatically incremented by one based on DepartmentID

This is my table.

create table tbl_employee

(

empno varchar(10) primary key,

empname varchar(40),

deptid varchar(10),

designation varchar(40),

salary int

)

This is my procedure. It will insert Empno automatically increased by one based on deptid.

i.e. For deptA, empno will be deptA,deptA2,deptA3,……

For deptB, empno will be deptB1,,deptB2,deptB3,……

create proc InsertToTable

(@eName varchar(40),@eDeptId varchar(20),@eDesignation varchar(40),@eSalary int)

as

begin

declare @count varchar(10);

set @count=(select COUNT(designation) from tbl_employee where designation=@eDesignation)+1

insert into tbl_employee values (@eDesignation+@count,@eName,@eDeptId,@eDesignation,@eSalary)

end

Good luck………….

 

Procedure with output parameter in ASP.NET

My Procedure is follows,

create proc outparam(@a int,@b int,@c int out)

as

set @c=@a+@b;

In Code behind,

SqlCommand cmd = new SqlCommand(“outparam”, con);

con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@a”, SqlDbType.Int).Value = TextBox1.Text;

cmd.Parameters.Add(“@b”, SqlDbType.Int).Value = TextBox2.Text;

cmd.Parameters.Add(“@c”,SqlDbType.Int);

cmd.Parameters[“@c”].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

TextBox3.Text = cmd.Parameters[“@c”].Value.ToString();

con.Close();

Good luck……………..

 

Stored procedure in ASP.NET

First of all you create one procedure in sql.

Then by following code we’ll use the stored procedure in ASP.NET. The main advantage of using stored procedure in ASP.NET is that it is fast and we hide the SQL queries in programming.

SqlConnection con;

SqlCommand cmd = new SqlCommand();

con = new SqlConnection(“server=””;uid=sa;pwd=””;database=product”);

cmd = new SqlCommand(“Procedurename”, con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@name”, SqlDbType.VarChar).Value = TextBox1.Text; //1st parameter to proc

cmd.Parameters.Add(“@passwd”, SqlDbType.VarChar).Value =TextBox2.Text; //2nd parameter to proc

cmd.Parameters.Add(“@email”, SqlDbType.VarChar).Value = TextBox4.Text; //3rd parameter to proc

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Best of luck………..