make it correct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

make it correct

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-07 : 08:08:33

can anyone make it correct?
can anyone make it more effiect. my db version is 2000 and i am using sql server management studio.
/*=========================================================================================================================

Name : Mehmood Ahmed
Date : 06-April-2010
============================================================================================================================*/
DECLARE @CONS_NME VARCHAR(100)
DECLARE @FK_TBL_NME VARCHAR(100)
DECLARE @FK_COL_NME VARCHAR(100)
DECLARE @PK_TBL_NME VARCHAR(100)
DECLARE @PK_COL_NME VARCHAR(100)
DECLARE @count_repeated_cons int
DECLARE @repeated_cons_nme varchar(200)
DECLARE @concat_col_names varchar(200)
DECLARE @complete_cols varchar(200)
Declare @fkey int
DECLARE @pos int

DECLARE get_data_cur CURSOR FOR
select Object_Name(f.constid) ,
Object_Name(f.fkeyid) ,f.fkey,
c1.name ,
Object_Name(f.rkeyid) ,
c2.name
from dbo.sysforeignkeys f
inner join dbo.syscolumns c1 On c1.id = f.fkeyid And c1.colid = f.fkey
inner join dbo.syscolumns c2 On c2.id = f.rkeyid And c2.colid = f.rkey
where Object_Name(f.fkeyId) = 'contract' /* and Object_Name(f.constid) = 'R_1425' */
order by 1,2,3,4
set @pos = 0
open get_data_cur
fetch get_data_cur into @CONS_NME, @FK_TBL_NME,@fkey,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME

SET @concat_col_names = @FK_COL_NME
while (@@fetch_status = 0 )
BEGIN

SELECT @repeated_cons_nme = Object_Name(f.constid), @count_repeated_cons = Count(1) /*AS Counts*/
FROM dbo.sysforeignkeys f
INNER JOIN dbo.syscolumns c1
On c1.id = f.fkeyid And c1.colid = f.fkey and object_name(f.fkeyId) = 'contract' and Object_Name(f.constid) = @CONS_NME
INNER JOIN dbo.syscolumns c2
On c2.id = f.rkeyid And c2.colid = f.rkey
GROUP BY f.constid
HAVING COUNT(1) >= 1

select @repeated_cons_nme, @count_repeated_cons ,@CONS_NME, @FK_TBL_NME,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME

IF @COUNT_REPEATED_CONS = 1 and @FK_COL_NME = @PK_COL_NME and @repeated_cons_nme = @CONS_NME
BEGIN
SET @concat_col_names = @FK_COL_NME

EXEC(' AlTER TABLE ' + @FK_TBL_NME + ' DROP CONSTRAINT ' + @CONS_NME )
EXEC(' AlTER TABLE ' + @FK_TBL_NME + ' WITH NOCHECK ADD CONSTRAINT ' + @CONS_NME + '
FOREIGN KEY ( ' + @concat_col_names + ' )
REFERENCES ' + @PK_TBL_NME + ' ( ' + @concat_col_names + ' ) ON DELETE CASCADE ')

END

SET @concat_col_names = ''
END
ELSE IF @COUNT_REPEATED_CONS > 1 and @FK_COL_NME = @PK_COL_NME and @repeated_cons_nme = @CONS_NME
BEGIN

set @pos = @pos + 1
if (len(@concat_col_names) > 0)
begin
SET @concat_col_names = COALESCE(@concat_col_names,'') + ' , ' + @FK_COL_NME
set @complete_cols = @concat_col_names
select @concat_col_names concat_total, @FK_COL_NME fk_col, @COUNT_REPEATED_CONS cout_repeated
IF @pos <= @COUNT_REPEATED_CONS
BEGIN

EXEC(' AlTER TABLE ' + @FK_TBL_NME + ' DROP CONSTRAINT ' + @CONS_NME )
EXEC(' AlTER TABLE ' + @FK_TBL_NME + ' WITH NOCHECK ADD CONSTRAINT ' + @CONS_NME + '
FOREIGN KEY ( ' + @concat_col_names + ' )
REFERENCES ' + @PK_TBL_NME + ' ( ' + @concat_col_names + ' ) ON DELETE CASCADE ')

END

end
else if (len(@concat_col_names) = 0)
begin
SET @concat_col_names = @FK_COL_NME
end

END

fetch get_data_cur into @CONS_NME, @FK_TBL_NME,@fkey,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME

END

close get_data_cur
deallocate get_data_cur

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-07 : 08:12:17

You need to tell us what is the problem with the code ?

Syntax error ?
Any error message ?
What is the code doing ?
It did not give you the correct result ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources