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)

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