CREATE proc [dbo].[usp_DBDetailsRevised]  
AS        
SET NOCOUNT ON

DECLARE @id INT, @cnt INT  
DECLARE @FullName VARCHAR(255), @SchemaName VARCHAR(255), @TableName VARCHAR(255), @sql NVARCHAR(4000), @temp VARCHAR(900), @pcol VARCHAR(255)

CREATE TABLE #temptable (
                          TableName VARCHAR(255)
                        , TotalColumns INT
                        , TotalRows INT
                        , PrimaryKeyCols VARCHAR(900)
                        )    

DECLARE tempCursor CURSOR FOR    
  SELECT schema_name(schema_id) + '.' + [name] AS [FullName]
           , schema_name(schema_id) AS [SchemaName]
           , [name] AS [TableName]
           , object_id AS id 
    FROM sys.Tables
    WHERE [name] NOT LIKE 'sys%'
        AND [name] NOT LIKE 'SSIS%'

OPEN tempCursor        
  FETCH NEXT FROM tempCursor INTO @FullName, @SchemaName, @TableName, @id
  WHILE(@@fetch_status=0)        
  BEGIN        
    SET @cnt=0     
    SET @sql='select @cnt = count(*) from ' + @FullName    
    EXEC sp_executesql @sql, N'@cnt int out', @cnt OUT         

    SET @temp=''  
    DECLARE intemp CURSOR FOR    
    SELECT a.Column_Name 
      FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a, INFORMATION_SCHEMA.TABLE_CONSTRAINTS b 
      WHERE b.constraint_type='PRIMARY KEY' 
        AND a.constraint_name = b.constraint_name 
        AND a.table_schema = LTRIM(RTRIM(@SchemaName))
        AND a.table_name=LTRIM(RTRIM(@TableName))
         
    OPEN intemp  
      FETCH NEXT FROM intemp INTO @pcol  
      WHILE(@@FETCH_STATUS=0)        
      BEGIN        
        SET @temp = @temp + ',' + @pcol  
        FETCH NEXT FROM intemp INTO @pcol  
      END  
    CLOSE intemp  
    DEALLOCATE intemp  

    SET @temp='''' + SUBSTRING(@temp,2,900) + ''''  

    SET @sql='insert into #temptable (TableName, PrimaryKeyCols, TotalRows, TotalColumns) values (''' + @FullName + ''',' + @temp + ',' + cast(@cnt as varchar) + ','      

    SELECT @cnt=count(*) 
      FROM sysColumns 
      WHERE id=@id

    SET @sql=@sql + cast(@cnt AS VARCHAR) + ')'
    EXEC sp_executesql @sql

    FETCH NEXT FROM tempCursor INTO @FullName, @SchemaName, @TableName, @id
  END      
CLOSE tempCursor    
DEALLOCATE tempCursor    

SELECT * 
  FROM #tempTable
  ORDER BY tablename        

DROP TABLE #temptable        
SET NOCOUNT OFF