February 16, 2015 at 10:10 pm
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
February 16, 2015 at 10:54 pm
vipin_jha123 (2/16/2015)
Hi I am looking for standard sql code for below 2 concern1- 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
Change is inevitable... Change for the better is not.
February 16, 2015 at 11:18 pm
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
February 16, 2015 at 11:31 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply