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

 

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.

Concatenate Values in Single Cell in SQL

DECLARE @t TABLE (EmpId INT, EmpName VARCHAR(100)) — Here @t is a static table with Columns EmpId & EmpName
INSERT @t VALUES
(1, ‘Bhavik’),(1, ‘Mayank’),(1, ‘Keyur’),(2, ‘Sudarshan’),(2, ‘Sohan’) –Values are inserted in the Table.
SELECT distinct
EmpId,
(
SELECT EmpName+’,’
FROM @t t2
WHERE t2.EmpId = t1.EmpId
FOR XML PATH(”)
) Concatenated –Names with Same ID are Concatenated and Displayed in Single Cell.
FROM @t t1