Replacing columnn name programmatically

Hi,

The following script does not return any resultset against a test db
while I know for a fact tables with letter "aaa" has columns that
contains "ccc".
What's wrong? the the inner cursor?

Thanks.

-- get all tbls with letter aaa
declare @.tbl varchar(8000)
declare tblCursor cursor for
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%aaa%'

open tblCursor
fetch next from tblCursor
into @.tbl

while (@.@.fetch_status = 0)
begin

-- get all columns with letter ccc and replace it with nothing /
remove it
declare @.tbuffer varchar(4000)
declare @.cbuffer varchar(8000)

declare abnormal_cols cursor for
SELECT o.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
and o.id = object_id('+@.tbl')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer

while (@.@.fetch_status = 0)
begin
-- EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
'COLUMN';
-- test
print @.tbuffer + ', ' + @.cbuffer;
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer
end

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @.tbl

end
close tblCursor
deallocate tblCursor;Hi

I can't see why there are two cursors here, try:

SELECT o.name, Replace(c.name,'ccc','') as NewName, c.name as OldName
FROM sysobjects o JOIN syscolumns c ON o.id = c.id
JOIN syscolumns a ON o.id = a.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
AND a.name LIKE '%aaa%'

John

"Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0310211451.2f59f9c4@.posting.google.c om...
> Hi,
> The following script does not return any resultset against a test db
> while I know for a fact tables with letter "aaa" has columns that
> contains "ccc".
> What's wrong? the the inner cursor?
> Thanks.
>
> -- get all tbls with letter aaa
> declare @.tbl varchar(8000)
> declare tblCursor cursor for
> SELECT name
> FROM sysobjects
> WHERE xtype = 'U'
> AND name LIKE '%aaa%'
> open tblCursor
> fetch next from tblCursor
> into @.tbl
> while (@.@.fetch_status = 0)
> begin
> -- get all columns with letter ccc and replace it with nothing /
> remove it
> declare @.tbuffer varchar(4000)
> declare @.cbuffer varchar(8000)
> declare abnormal_cols cursor for
> SELECT o.name, c.name
> FROM sysobjects o
> JOIN syscolumns c ON o.id = c.id
> WHERE o.xtype = 'U'
> AND c.name LIKE '%ccc%'
> and o.id = object_id('+@.tbl')
> -- ORDER BY c.name
> open abnormal_cols
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> while (@.@.fetch_status = 0)
> begin
> -- EXEC sp_rename
'+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
> 'COLUMN';
> -- test
> print @.tbuffer + ', ' + @.cbuffer;
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> end
> close abnormal_cols
> deallocate abnormal_cols;
> fetch next from tblCursor
> into @.tbl
> end
> close tblCursor
> deallocate tblCursor;