Query to display total number of rows for each tables in a database - SQL

Following query displays the total number of rows for each table in a database.

• Open SSMS
• Select database
• Execute the below Query:

             select sysobjects.name 'Table Name',rowcnt 'Total # Rows' from sysindexes 
             inner join  sysobjects on sysindexes.id= sysobjects.id
             where indid < 2
             and sysobjects.xtype='U'
            Order by 'Total # Rows' desc

The above query will return the list of all the tables in a database along with total numbers of rows in it.

Delete duplicate rows using Row_number and CTE in SQL

                   We can use Row_Number to delete duplicate rows from a table, as we know Row_Number function allocates serial number based on a key column.

Consider a table named 'EmpDetails' which has data as below:

As seen in the above screen shot there are duplicate records for EmpId 1,2 and 3

By using CTE and Row_number function we can remove the duplicate records from the above table. The query to remove duplicate values is as follow:

Query:

With CTE as
(select *,ROW_NUMBER() over(partition by empid order by empid) as Row_num from dbo.EmpDetails)

delete from CTE where Row_num > 1

This query will delete the duplicate rows from the EmpDetails table

Now, if we see the data in the EmpDetails Table it will contain only unique values in EmpID column.

Select * from dbo.EmpDetails