Replacing columnn name programmatically

[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]

Re: Replacing columnn name programmaticall y

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@box frog.com> wrote in message
news:fc254714.0 310211451.2f59f 9c4@posting.goo gle.com...[color=blue]
> 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[/color]
'+@tbuffer+'.['+@cbuffer+']','+Replace(+@c buffer+','%ccc% ','')',[color=blue]
> '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;[/color]