vwRowCount (dependencies included)

This simple view queries sys.indexes to get a rowcount of all tables in the database.  This is preferable to using SELECT Count(*) because this method will not use a table scan or index scan.

There is one dependency for object udfFormatInteger() which is included here for convenience.

USAGE

       SELECT *

       FROM vwRowCount

       ORDER BY Rows desc

USE Admin

IF Object_ID('dbo.udfFormatInteger') Is Not Null

       DROP FUNCTION dbo.udfFormatInteger

go

CREATE FUNCTION dbo.udfFormatInteger(

       @In bigint)

RETURNS varchar(20)

AS

/*    DATE              AUTHOR            REMARKS   

      8/19/15           PPaiva            Initial creation.

    

      DESCRIPTION

            Formats a given integer with commas.

                  Examples:

                              IN                OUT

                              123               123

                            12345            12,345

                           -54321           -54,321

      USAGE

            SELECT dbo.udfFormatInteger(1234567890)

            SELECT dbo.udfFormatInteger(12345)

            SELECT dbo.udfFormatInteger(-54321)

            SELECT dbo.udfFormatInteger(0)

*/

BEGIN

      DECLARE @Out varchar(20),

                  @sIn varchar(20),

                  @Balance varchar(20),

                  @CurrTextLen smallint,

                  @IsNegative bit

      IF @In < 0

            BEGIN

                  SET @IsNegative = 1

                  SET @In = Abs(@In)

            END

      SET @sIn = Convert(varchar, @In)

      SET @CurrTextLen = Len(@sIn)

      SET @Out = ''

      SET @Balance = @sIn

      IF @CurrTextLen > 3

            BEGIN

                  WHILE 1 = 1

                        BEGIN

                              SET @Out = ',' + Right(@Balance, 3) + @Out

                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)

                              IF @CurrTextLen > 3

                                    CONTINUE

                              ELSE

                                    BEGIN

                                          SET @Out = @Balance + @Out

                                          BREAK

                                    END 

                        END

            END

      ELSE

            SET @Out = @sIn

      IF @IsNegative = 1

            SET @Out = '-' + @Out

      RETURN @Out

END

go

IF Object_ID('dbo.vwRowCount') Is Not Null

       DROP VIEW dbo.vwRowCount

go

CREATE VIEW dbo.vwRowCount

AS

/*     DATE          AUTHOR        REMARKS

       4/2/16        PPaiva        Initial creation.

       DESCRIPTION

              Low-footprint row count (without table or index scan) for a given database.

       SELECT *

       FROM vwRowCount

       ORDER BY Rows desc

*/

SELECT  Convert(varchar, ServerProperty('ServerName')) as Instance,

              DB_NAME() AS DB,

              SCHEMA_NAME(t.schema_id) AS SchemaName,

              t.name AS TableName,

              '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS FullTableName,

              dbo.udfFormatInteger(i.Rows) RowsFMT,

              i.Rows,

              t.object_id

FROM sys.tables t

JOIN sys.sysindexes i

      ON  t.object_id = i.id

      AND i.indid < 2         -- 0 = heap, 1 = has clustered index, >1 is non-clustered index