Posted by: rawbee November 20, 2008
Cursor in T-sql
Login in to Rate this Post:     0       ?        

absoutly Techi....

don't look at how to use cursor. first of all think how you can avoid curssor .like using Temp table,Temp table variable or doing sub queries.. as you know when you use curssor, preformance become slower.... ..

ani BTW here is code for Truncate all Table in SQL 2005. might help you guys later on..

How to used:

1.- Create table to hold constraints values

2.-Populate table

3.- Create cursor to remove constraints

4.- truncate all data
 
 

Use DatabaseName --Temporary table to hold constraints info most of the time at a different location

-- or database

--This could be a temp table however set as static

IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')

truncate table T_FK_Xref

go

--Create Table to store constraint information

IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')

Create table DatabaseName.dbo.T_FK_Xref (

ID int identity (1,1),

ConstraintName varchar (255),

MasterTable varchar(255),

MasterColumn varchar(255),

ChildTable varchar(255),

ChildColumn varchar(255),

FKOrder int

)

go

--Store Constraints

insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)

SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable

,sc2.name MasterColumn

,object_name(fkeyid) ChildTable

,sc1.name ChildColumn

,cast (sf.keyno as int) FKOrder

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

go

use databaseName --Database to removed constraints

go

---Ready to remove constraints

declare @ConstraintName varchar (max) -- Name of the Constraint

declare @ChildTable varchar (max) -- Name of Child Table

declare @MasterTable varchar (max)--Name of Parent Table

declare @ChildColumn varchar (max)--Column of Child Table FK

declare @MasterColumn varchar (max)-- Parent Column PK

declare @FKOrder smallint -- Fk order

declare @sqlcmd varchar (max) --Dynamic Sql String

 

-- Create cursor to get constraint Information

declare drop_constraints cursor

fast_forward

for

SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable

,sc2.name MasterColumn

,object_name(fkeyid) ChildTable

,sc1.name ChildColumn

,cast (sf.keyno as int) FKOrder

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

open drop_constraints

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

while @@Fetch_status = 0

begin

-- Create Dynamic Sql to drop constraint

--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'

select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName

If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)

exec (@sqlcmd)

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

end

close drop_constraints

deallocate drop_constraints

go

--Removed CHECK Constraint-------------------------

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints

print 'All Constraints Disable'

go

--truncate All tables if trying to empty the database

--- Ensure the T_X_ref database is located on a different database

------------- Truncate All Tables from Model ----------------

-----To limit tables a table with sub model tables must be created and used joins-----

EXEC sp_MSForEachTable 'truncate TABLE ? '

print 'All tables truncated'

go

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

And we can find/delete duplicate record without cursor

DELETE
    FROM    MyTable
    WHERE    ID NOT IN
    (
    SELECT    MAX(ID)
        FROM        MyTable
        GROUP BY    DuplicatevalueColumn1, DuplicateValueColumn2,
                DuplicateValueColumn2)

 

ENJOY IN SQL WORLD/.........

Read Full Discussion Thread for this article