TableNameWithRecordCount in SQL Server

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--Sp_TableNameWithRecordCount 'backup'

CREATE PROC [dbo].[Sp_TableNameWithRecordCount]

@DatabaseName varchar(50)

AS

begin

declare @@Query varchar(max)

set @@Query = '

SELECT

t.NAME AS TableName,

SUM(p.rows) AS [RowCount]

FROM ['+

@DatabaseName+'].sys.tables t

INNER JOIN [' +

@DatabaseName+'].sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

['+

@DatabaseName+'].sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

WHERE

i.index_id <= 1

GROUP BY

t.NAME, i.object_id, i.index_id, i.name

HAVING SUM(p.rows) > 0

ORDER BY

tablename'

print @@Query

exec(@@Query)

end