Category Archives: SQL Cursors

Rename a table Column in MS SQL

How to rename a MS SQL server database table column name after the table creation??
You can rename a column regardless of it is null or contains any data.

We need to use a System stored procedure to rename. Any command with alter keywords won’t work.

Code
EXEC sp_rename ‘tableName.[oldColumnName]’, ‘newColumnName’, ‘COLUMN’

Example:
EXEC sp_rename ‘Ultra_tblPressRelease.[uShowStatus]’, ‘uShowInA2B’, ‘COLUMN’

The above line will rename the existing column named uShowStatus of table tblPressRelease to uShowInA2B.

There will be no adverse effect of this procedure on your table data.
But you have to update all your SQL Cursors and Stored Procedures with the new column name (if any).

Advertisements

Update database data using SQL Cursors based on conditions

Recently one of my MSSQL database got SQL Injection attacks.
Hacker put some JavaScript code in all row data to get hits on his website (which is apparently blocked, and soon my site will also be got banned if it keep happening for a long time).

Because the database has a huge amount of data, i can’t update table data using ExecuteNonQuery commands as it will result in a great overload on database and network resources.

So I decided to make something logical which is fast and logical way to handle this situation, and at last i ended up with writing a cursor.

This cursor finds a specific string and replaces it with null.

Scenario:

Create a table with sample data

create table tblDataUpdateTest
(
slno int identity(1,1),
cName varchar(50),
cRemarks varchar(50),
)

insert into tblDataUpdateTest (cName, cRemarks) values (‘name1′,’some text here BADCODE’)
insert into tblDataUpdateTest (cName, cRemarks) values (‘name2′,’raBADCODEhul’)
insert into tblDataUpdateTest (cName, cRemarks) values (‘name3′,’dj BADCODEis devil’)

select * from tblDataUpdateTest


Create Cursor to update database table

Declare @@counter int
set @@counter=0
Declare @@slno int
Declare @@cRemarks varchar(100)

Declare tmepTbl cursor
For
Select slno,cRemarks from tblDataUpdateTest

Open tmepTbl /* Opening the cursor */

fetch next from tmepTbl
into @@slno,@@cRemarks

while @@fetch_Status-1
begin

Update tblDataUpdateTest
set cRemarks = Replace(@@cRemarks,’BADCODE’,”)
where slno = @@slno

fetch next from tmepTbl
into @@slno, @@cRemarks

set @@counter=@@counter+1
end
close tmepTbl
Deallocate tmepTbl

Conclusion:

By running this cursor all the occurrences of BADCODE will be eliminated from the specified table.

Note:
I used word BADCODE here but in my actual data it was a java script tag which was creating all the problem.

For more articles updates and sourcecodes please visit dotnetcoderoom