Dentro da janela de consulta do SQL SERVER basta rodar o comando abaixo
CREATE TABLE #TMP_CONTA_TABELA
(TABELA VARCHAR(200),
REGISTRO INT)
DECLARE @COMANDO VARCHAR(500)
DECLARE C_CountTable CURSOR FAST_FORWARD FOR
SELECT distinct
'SELECT TABELA = ''' + SU.NAME + '.' + SO.NAME + '''
, REGISTRO = COUNT(*) FROM ' + SU.NAME + '.' + SO.NAME
FROM SYSOBJECTS SO INNER JOIN SYSUSERS SU
ON SO.UID = SU.UID
AND SO.XTYPE = 'U'
AND SO.NAME <> 'dtproperties'
OPEN C_CountTable
FETCH NEXT FROM C_CountTable
INTO @COMANDO
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('INSERT INTO #TMP_CONTA_TABELA '+ @COMANDO)
FETCH NEXT FROM C_CountTable
INTO @COMANDO
END
CLOSE C_CountTable
DEALLOCATE C_CountTable
SELECT * from #TMP_CONTA_TABELA ORDER BY 2 DESC
fonte: https://www.devmedia.com.br/quantos-registros-existem-sua-tabela-no-sql-server/5345
