DELETE Duplicate Records Using CTE

Syntax
With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1  

Example :

BEGIN
   --Declare Statemant
    DECLARE @tblduplicatedata TABLE(name VARCHAR(50),age INT,salary INT);

    --Insert Statements
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Geet',15,85000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    
    WITH cte_duplicate (name, age, salary,rownumber)
        AS (
        SELECT name,age,salary, row_number()OVER(PARTITION BY name, age , salary ORDER BY name, age , salary)AS rank FROM @tblduplicatedata
        )
        DELETE FROM cte_duplicate WHERE rownumber<>1  

        SELECT * FROM @tblduplicatedata
END
 

how to know collation of a/all tables

List all table and collation name list 
 
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='SQL_Latin1_General_CP1_CI_AS'


SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='Latin1_General_CI_AI'

Get collation name of a particular table
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE  object_id = OBJECT_ID('table_name')

How to get all table names in a database in sql server

SELECT db_name(), name,* FROM sys.tables;