Replacing columnn name programmaticall y

[Doug Baroter]

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('+@tb l')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @tbuffer,@cbuff er

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

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @tbl

end
close tblCursor
deallocate tblCursor;

[John Bell]

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" <qw********@box frog.com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

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('+@tb l')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @tbuffer,@cbuff er

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

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @tbl

end
close tblCursor
deallocate tblCursor;