Tech Insight !

Understanding sys.objects:

Sys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.

Using Sys.objects returns list of all database objects and its types, type can be either of given below:

DB OBJECT TYPES

F     FOREIGN_KEY_CONSTRAINT

IT    INTERNAL_TABLE

PK    PRIMARY_KEY_CONSTRAINT

S     SYSTEM_TABLE

SQ    SERVICE_QUEUE

U     USER_TABLE

V     VIEW

How to DELETE all User Tables , stored procedures , UDF’s and Views using cursor

Use [database name]

declare @q as
nvarchar(max)

declare @name nvarchar(max);

declare @type nvarchar(max);

declare cur cursor
for select name ,type from sys.objects where type in(‘p’,‘fn’,‘v’,‘u’);

open cur;

fetch next from cur into @name,@type

while @@fetch_status = 0

begin

if(@type=‘p’)

begin

set @q=N‘drop procedure ‘ + @name;

end

if(@type=‘fn’)

begin

set @q=N‘drop function ‘ + @name;

end

if(@type=‘v’)

begin

set @q=N‘drop view ‘ + @name;

end

if(@type=‘u’)

begin

set @q=N‘drop table ‘ + @name;

end

exec sp_executesql @q;

fetch next from cur into @name,@type

end

close cur;

deallocate cur;

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.

Normalization: is a process of organizing data and minimizing redundancy

De-normalization: is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Stored Procedure: is a named group of T-SQL statements which can be created and stored in Database as an object.

Primary Key: is a unique identifier of a row in a DB table, [it can’t be NULL]

Unique key: forces uniqueness to a respective table column, [it can be NULL]

Foreign Key: a foreign key in 1 table refers to the primary key in other table, Used to force referential integrity.

Inner join: exists in both tables

Left Outer join: all records from left side table + matched rows from right side table (totals number of rows will be same as left table)

Right Outer join: all records from right side table + matched rows from left side table (totals number of rows will be same as right table), it’s a mirror image of left outer join

Full Outer join: all records from left side table + all records from right side table, weather matched or not

Cross join: returns [left table rows * right table rows], a Cartesian product of both tables

Self join: when table joins to itself using diff aliases to avoid confusion

Union: selects only distinct records from both tables

Union all: selects all records from both tables

View: is a subset of a table, can be used to retrieve data, insert or Update data. Can contain multiple select statements inside

Trigger: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

Cursor: is a database object used to loop trough records on row by row bases.

Index: pointers to data records, represents structure of how data get stored physically in a table

Linked server: is a concept of adding other remote server to a group to query DB’s of both servers together

Collation: set of rules that determines how data stores & compares in database

Collation types: case sensitive, accent sensitive, kana sensitive, width sensitive

Data ware housing:

1. Record should Never delete from DB
2. All records must be linked
3. Once committed records should be read-only
4. All changes made must be tracked with time

User defined function (UDF): is a bunch of T-SQL statements which accepts 0 or more parameters and returns a scalar data value or table.

DDL: data definition language – e.g. TRUNCATE command is a DDL command

DML: data manipulation language – e.g. INSERT, UPDATE & DELETE are DML commands

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.

with cte (empID,manID,depth,hierarchy) as

(

select e.employeeID,e.managerID ,1 , cast(e.employeeID as nvarchar(max))as hierarchy

from humanresources.Employee as e

where managerID is null

union all

select emp.employeeID,emp.managerID ,depth+1 , hierarchy + ‘/’ +cast(emp.employeeID as nvarchar(max)) as hierarchy

from humanresources.Employee emp

inner join cte

on emp.managerID=cte.empID

)

select * from cte order by depth

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.

Handler code: image-handler.ashx

<%@ WebHandler Language=”C#” Class=”image_handler” %>

using System;

using System.Web;

using System.IO;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Configuration;

public class image_handler : IHttpHandler

{

public void ProcessRequest(HttpContext context)

{

string xStrCon = System.Configuration.ConfigurationManager.ConnectionStrings["conStrImage"].ConnectionString;

SqlConnection xCon = new SqlConnection(xStrCon);

xCon.Open();

SqlCommand xCom = new SqlCommand(“select * from images where”, xCon);

SqlDataAdapter xAda = new SqlDataAdapter(xCom);

DataSet ds = new DataSet();

xAda.Fill(ds);

// Image processing starts

byte[] bytImage = ReadFile(context.Server.MapPath(“banner.JPG”));

xCom.CommandText = “insert into images values(2,’kensington’,@imagedata)”;

xCom.Parameters.Add(new SqlParameter(“@imagedata”, (object)bytImage));

xCon.Close();

//Display image from SQL server

byte[] imageData = (byte[])ds.Tables[0].Rows[0][2];

context.Response.Clear();

context.Response.ContentType = “image/JPEG”;

context.Response.OutputStream.Write(imageData, 0, imageData.Length);

}

byte[] ReadFile(string sPath)

{

//Initialize byte array with a null value initially.

byte[] data = null;

//Use FileInfo object to get file size.

FileInfo fInfo = new FileInfo(sPath);

long numBytes = fInfo.Length;

//Open FileStream to read file

FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

//Use BinaryReader to read file stream into byte array.

BinaryReader br = new BinaryReader(fStream);

//When you use BinaryReader, you need to supply number of bytes to read from file.

//In this case we want to read entire file. So supplying total number of bytes.

data = br.ReadBytes((int)numBytes);

return data;

}

public bool IsReusable

{

get

{

return false;

}

}

}

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.

NOTE :

If you are in need of any Web Development feel free to Inquire us . Dhanashree Inc. Expertise in Asp.net Development, Php Development, Website designing, Open Source customisation. Dhanashree Inc can be our offshore development company / outsourcing web development company, hire dedicated web programmers.

Above information is for knowledge sharing if you have problem / issue / suggestion please intimate us with details for proper and prompt action.