title: Count duplicate records from any table using stored procedure
body: <p>I have a stored procedure which can check any table on duplicates. It returns a result set with all the duplicate rows from the given table. </p>
  
  <p>What I want now is to count the amount of duplicates, get the table name &amp; rundate query  and insert it into a table.</p>
  
  <p>The problem I'm facing currently is that the SP returns a set with a variable amount of columns. Therefore I can't insert the result of the SP in a predefined temptable. </p>
  
  <p>Does anyone know a (smarter) way to do this?</p>
  
  <p><strong>Expected result</strong></p>
  
  <p>|tableName|Date|sumDupRows|</p>
  
  <p><strong>SP I'm using:</strong> </p>
  
  <pre><code>create proc [dbo].[sp_duplicates] @table nvarchar(50) as
  
  declare @query nvarchar(max)
  declare @groupby nvarchar(max)
  
  set @groupby =  stuff((select ',' + name
                  FROM sys.columns
                  WHERE object_id = OBJECT_ID(@table)
                  FOR xml path('')), 1, 1, '')
  
  set @query = 'select *, count(*) as duplicates
                  from '+@table+'
                  group by '+@groupby+'
                  having count(*) &gt; 1'
  
  exec (@query)
  GO
  </code></pre>