Pages

Tuesday 29 January 2013

Using Stored Procedure Counting Number of Rows of All Tables of Database in SQL Server

CREATE PROCEDURE dbo.listTableRowCounts
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(255)
    SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
    EXEC(@SQL)

    CREATE TABLE #foo
    (
        tablename VARCHAR(255),
        rc INT
    )
    
    INSERT #foo
        EXEC sp_msForEachTable
            'SELECT PARSENAME(''?'', 1),
            COUNT(*) FROM ?'

    SELECT tablename, rc
        FROM #foo
        ORDER BY tablename ASC

    DROP TABLE #foo
END

No comments:

Post a Comment