Select the table which duplicate rows are present in that table(only display duplicate rows without repetition )?

If i get your question right, you can use Distinct ( Select Distinct * from Table). it will show only unique rows data. 

SELECT field1, field2, COUNT(*)FROM table_name GROUPBY field1, field2 HAVING COUNT(*) =2

will show all only duplicated (inserted twice) rows. You must specify all fields with duplicated values.

To show all rows mutliplied (inserted twice and more times) :

SELECT field1, field2, COUNT(*)FROM table_name GROUPBY field1, field2 HAVING COUNT(*) >1

select f1,f2,f3 from tab group by fields..... having count(*) >1

you will get all duplicate rows by above...and then you can insert them into a tempdb...table or # table...then further you can query like select distinct f1,f2,f3 from tempdb..table or # table...is one possible solution.

 one other way may be...use the same above query and use intersect between the other query like select distinct f1,f2,f3 from table.,,should also work....not very sure as I am out of touch since long time, but logically it should work.

thanks for the invitation. 

for this you have to use Group by and distinct

We can use distinct function to eliminate the duplicate values

If I understood you correctly you should use distinct function to eliminate duplicate rows in the query, like this: select distinct * from ... but in this case distinct will check all columns and if some columns contains non duplicate data it won't eliminate duplicate rows, then try using window functions like row_number() or ranking functions like dense_rank.