About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Monday, April 11, 2016

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


No comments:

Post a Comment