Drop Column and Update column in sql

Question

Drop Column and Update column in sql

vipin_jha123

Hall of Fame

Points: 3085
More actions
February 16, 2015 at 10:10 pm

#297839

Hi I am looking for standard sql code for below 2 concern
1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx
2-I want to update all the table where table name start with ArchiveBbx
example:-
Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))
Please help me to achive my scenario
thanks in advance 🙂
Regards,
Vipin jha

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

vipin_jha123 (2/16/2015)
Hi I am looking for standard sql code for below 2 concern
1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx
2-I want to update all the table where table name start with ArchiveBbx
example:-
Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))
Please help me to achive my scenario
thanks in advance 🙂
Regards,
Vipin jha

I'm confused. First, why would you in step 1 want to drop the RowCheckSum column from all tables that follow a pattern and then think you can update that now dropped column in step 2? Or are you saying that "drop to all tables" is your way of saying that you want to add the column to the tables? If the later, you really need to stop using the word "drop" for such a thing because "drop" usually means "to remove". It's not like dropping a letter to someone.

My other question is why on this good green Earth are you using MD5? It's one of the slowest hashes there is and suffers from much more non-uniqueness than most any of the others.

Other than that, you've written most of what you need. You just need to read the table names from one of the system views and then a little dynamic SQL to loop through them. Have a look at sys.objects and INFORMATION_SCHEMA.tables in Books Online for more information.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

Hi ,

I want to have chcecksum value of all the column in my table. In ssis pacakge I used script component MD5 , but now I have converted ssis to SQL Script, where I dint found any other approach.

in SSIS i used to get my checksum value like 6KHlSG7t4eTaduLySN0/FQ==

but in SQL I am getting checksum value like 0xFDA0F387AA72F5256D870014532C4E0C by using

HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003) AS varchar(max))) command.

if you can help me to get checksum value like 6KHlSG7t4eTaduLySN0/FQ== in sql , then I dont have to drop the column and update the same.

or is there any way to get cheksum value of all the column.

please help me

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

vipin_jha123 (2/16/2015)
Hi ,
I want to have chcecksum value of all the column in my table. In ssis pacakge I used script component MD5 , but now I have converted ssis to SQL Script, where I dint found any other approach.
in SSIS i used to get my checksum value like 6KHlSG7t4eTaduLySN0/FQ==
but in SQL I am getting checksum value like 0xFDA0F387AA72F5256D870014532C4E0C by using
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003) AS varchar(max))) command.
if you can help me to get checksum value like 6KHlSG7t4eTaduLySN0/FQ== in sql , then I dont have to drop the column and update the same.
or is there any way to get cheksum value of all the column.
please help me

The 0xnnnnnnn is a checksum and is likely the hexadecimal representation of what you were seeing in SSIS. Also, if you lookup HASHBYTES in Books Online (the help system that SQL Server uses), you'll see many more options other than MD5.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions