July 20, 2006 at 12:07 am
Hi Everybody,
I am writing a cursor for change the Zone_Id column in a table. I don't know this zone_id column may be present in some tables in the database with the primary key constraints. So i want to change the zone_id value in the whole database with a cursor.Below is my cursor, but this is giving a Primary key violation error unable to insert the record.If i write three cursors one for no check constrains another for update the column and another for check constraints the code is long. Is there any good idea? Thanks in advance.
--Cursor--
declare @tablname varchar(200)
declare @colname varchar(200)
declare @sqlstr1 varchar(200)
declare @sqlstr2 varchar(200)
declare @sqlstr3 varchar(200)
declare @tname varchar(200)
declare @fromid varchar(20)
declare @toid varchar(20)
set @colname='ZONE_ID'
set @fromid='10'
set @toid='15'
--declaring the cursor --
declare zonecursor cursor for
select B.table_schema+ '.'+B.TABLE_NAME from information_schema.tables a inner join information_schema.columns b
on a.table_name=b.table_name and a.table_type='BASE TABLE' AND b.column_name='ZONE_ID'
AND A.TABLE_SCHEMA='SAMPLE'
open zonecursor
fetch next from zonecursor into @tablname while @@fetch_status=0
begin
set @sqlstr1 = 'ALTER TABLE '+ @TablName + ' NOCHECK CONSTRAINT ALL'
exec (@sqlstr1)
set @sqlstr2='update '+ @tablname +' set ' +@colname +' = replace' + '('''+@colname+''','''+@fromid+''','''+@toid+''')'
exec (@sqlstr2)
set @sqlstr3 = 'ALTER TABLE '+ @TablName + ' CHECK CONSTRAINT ALL'
exec (@sqlstr3)
fetch next from zonecursor into @tablname
end
close zonecursor
deallocate zonecursor
go
Ramaa
July 20, 2006 at 2:02 am
Hi Rama,
You can use the procedure, sp_msforeachtable. In fact, you can probably use it in place of your cursor.
declare @cmd nvarchar(4000)
--disable all table constraints
exec sp_msforeachtable 'alter table ? nocheck constraint all'
--run update command. NOTE, you'll need to amend this to fit with your filter
set @cmd = 'if exists(select 1 from information_schema.columns where column_name = ''ZONE_ID'' and table_name = ''?'')'
set @cmd = @cmd + ' update ? set zone_id = replace(zone_id,10,15)'
exec sp_msforeachtable @cmd
--re-enable all table constraints
exec sp_msforeachtable 'alter table ? check constraint all'
Hope that helps,
July 20, 2006 at 4:11 am
As the error says, you are trying to change a column that has a foreign key constraint to another table.
N 56°04'39.16"
E 12°55'05.25"
July 20, 2006 at 10:25 pm
Hai Karl,
Good Morning and Thank U Very much for your answer, but i am getting an error when running the update statement. It is looking all the tables even the condition is there and error is Invalid column name and one more thing How to avoid Views from this query. Please.....
Ramaa
July 21, 2006 at 2:58 am
Hi Rama,
You need to include your other filters. Also, if you have a case-sensitive database, make sure the column names are the proper case.
Try this. Note the debug parameter. When set to 1 it will print the commands that would be run. You can use this to test the validity of the statements.
declare @cmd varchar(8000)
declare @debug bit
set @debug = 1 --set this to 1 for debugging, which will only print the command
set @cmd = 'if exists(select 1 from information_schema.columns where column_name = ''ZONE_ID'' '
set @cmd = @cmd + 'and ''['' + table_schema + ''].['' + table_name + '']'' = ''?'' and TABLE_SCHEMA = ''SAMPLE'')'
set @cmd = @cmd + char(10) + ' update ? set ZONE_ID = replace(ZONE_ID,10,15)'
if @debug = 1
set @cmd = 'print ''' + replace(@cmd,'''','''''') + ''''
exec sp_msforeachtable @cmd
--when debug is set to 1 it will print the statements that would be executed.
Hope this helps,
July 21, 2006 at 9:43 pm
Hi Karl
Good Morning, You are simply superb. The code is working.I have one more doubt on sp_msforeachtable. I am writing this code for deleting temporary files names starts with TMP but it is removing the other tables Where is the mistake? once again ThankYou very much for answering my stupid questions.
EXEC sp_MSforeachtable @command1= N'drop table ?',
@whereand = N' and EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE=''U'' AND UID=''5'' AND NAME LIKE ''TMP%'')'
Ramaa
July 22, 2006 at 1:53 am
Hi Rama,
Try this for your @whereand parameter:
@whereand = N'and XTYPE = ''U'' and UID = 5 and NAME like ''TMP%'''
To test it, use the following for your @command1 parameter:
@command1 = N'print ''drop table ?'''
Hope that helps,
July 22, 2006 at 3:18 am
Hi Karl,
You are simply Genius. It's worked very well.
Keep it up.Thank You Friend.
Thank U Sqlservercentral.com people.
Ramaa
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply