Check Collation Name of Columns in Database

Hey Friends just simply execute the below query within your database, it will show table name,column name and collation name.

QUERY :

SELECT [TABLE_NAME] = OBJECT_NAME([id]),
[COLUMN_NAME] = [name],
[COLLATION_NAME] = collation
FROM syscolumns
WHERE collation <> ‘your_database_collation_type’
AND collation IS NOT NULL
AND OBJECTPROPERTY([id], N’IsUserTable’)=1

OUTPUT :

Collation
List of Database Tables Name with their fields and Collation Name

 

Get Months First and Last Date in SQl

We just need to pass a particular date in this query to get months first and last date of that passed particular date.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()

 

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
‘First Day of Month’ AS Date_Type

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
‘Last Day of Month’

Reference :  (http://blog.SQLAuthority.com)

Encryption and Decryption By Password in SQL

Encryption and Decryption

  • Encryption and Decryption Function.

Encrypt Function :-

Create Function dbo.Fn_ENCRYPT(@str varchar(max))
returns varbinary(8000)
as begin
declare @obj varbinary(8000)
set @obj = ENCRYPTBYPASSPHRASE(‘Bhargav Patel’,@str)
return (@obj)
end

Decrypt Function :-

Create Function dbo.Fn_DECRYPT(@str varbinary(8000))
returns varchar(max)
as begin
declare @obj  varchar(max)
set @obj = DECRYPTBYPASSPHRASE(‘Bhargav Patel’,@str)
return (@obj)
end

Use of above Function

SELECT dbo.Fn_ENCRYPT(‘SQL SERVER’)

OUTPUT :

Encryption

SELECT dbo.Fn_DECRYPT

(0x01000000D898AC04CE7777E7A45D99912FB70CB10B559B83BF7ED253AB43B9FF022008E7)

OUTPUT :

Decryption

Encryption and Decryption in SQL

Cell-Level Encryption

  • The Data which are Stored on Database can be Encrypted and Decrypted on server .This are the Following methods :
  • Password :- Using a password is the least secure option because you use the same pass-phrase to encrypt and decrypt the data. If the stored procedures and functions aren’t encrypted, the pass-phrase can be accessed through the metadata.
  • Certificate :- Using a certificate offers strong protection and good performance. You can also associate a certificate with a user. The certificate must be signed by the DMK [The database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys].
  • Symmetric key :- Using a symmetric key is strong enough for most data security requirements and offers good performance. It uses the same key to encrypt and decrypt data.
  • Asymmetric key :- Using an asymmetric key provides strong protection because you use a different key to encrypt and decrypt the data. However, it negatively affects performance. Microsoft recommends that it shouldn’t be used to encrypt large values. An asymmetric key can be signed by the DMK or created using a password.

NOTE : I am also having this much of information to share and will post this methods in my next posts.

Mouse Scroll Event in C#

private void Form_Load(object sender, EventArgs e)
{

this.MouseWheel += new MouseEventHandler(pictureBox1_MouseWheel); //Create Mouse Scroll Event at Form Load.

}
void pictureBox1_MouseWheel(object sender, MouseEventArgs e)
{

try
{

if(e.Delta>0) //Scroll UP Event
{

BtnNext.PerformClick(); //Call Your Method.

}
else//Scroll DOWNEvent
{

BtnPrev.PerformClick();//Call Your Method.

}

}
catch (Exception se)
{}

}