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
