get only unique row and delete all duplicate row

delete from table1 where                
EmpID in(select cId from table1 group by cid having count(*) >1)

delete duplicate value in sql server

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY column1,
column)

Rename database in sql 2008



Rename Database in sql server 2008

1.       How to create database in sql server 2008

USE Master;
Go

CREATE DATABASE SALARYMANAGEM;
Go

2.       How  to rename database

ALTER DATABSE SALARYMANAGEM
MODIFY NAME=SALAYMANAGEMANT;
Go
3.       How  to drop database

Drop Database SALAYMANAGEMANT;
Go

Deepak Goyal

Cursor in SQL

We can say that cursor is row pointer in a set of rows (result set).Which points a single row at time in result set. But can move to other rows of the (result set) when required.

 To use cursor in SQL Store procedures. You need to follow the given steps.
  1.  Define a cursor
  2. Open the cursor to establish the result set
  3. Fetch the data into local parameter  as needed from the cursor.(one row at a time)
  4. Close the cursor when done.

Remove All User Defined Store procedures, Views and Triggers

interesting things  in SQL if you have a list of Store Procedures ,Views and triggers  in your database and reason  being you want to delete all Store procedures means you want to delete all the thing from data base. In that condition what you will do? It quiet simple you will delete one by one all the things. But it is so boring and time wasting. Let see how to write a simple Script to delete all Store procedures, views and triggers from database.

Remove All User Defined Store procedures.

Create  PROCEDURE [dbo].[usp_RemoveAllStoreProceduers]
AS
BEGIN
               
                declare @spName varchar(150);
  --Step 1 : Define Cursor (Create a result set of all procedures in database).
                declare cur cursor for select [name] from sys.objects where type='p'
                --Step 2 : Open Cursor
                open cur
  --Step 3 : Fatch data into local parameter
                fetch next from cur into @spName
                while @@fetch_Status =0
                begin
 -- Drop current store procedure in Cursor
                exec ('drop procedure '+ @spName)
                fetch next from cur into @spName
                end
                close cur
  --Close Cursor After done
                deallocate cur
  --Deallocate Cursor After done
END
Remove All User Defined View.

Create PROCEDURE [dbo].[usp_RemoveAllUserDefinedViews]
AS
BEGIN
               
                declare @viewName varchar(150);
  --Step 1 : Define Cursor (Create a result set of  all views in database).
                declare cur cursor for select [name] from sys.objects where type='v'
                --Step 2 : Open Cursor
                open cur
  --Step 3 : Fatch data into local parameter
                fetch next from cur into @viewName
                while @@fetch_Status =0
                begin
 -- Drop current view in Cursor
                exec ('drop view '+ @viewName)
                fetch next from cur into @ viewName
                end
                close cur
  --Close Cursor After done
                deallocate cur
  --Deallocate Cursor After done
E

.net training in jaipur