/*-----------------------------------------------------------------
-- Name : Get all table counts
-- Description : This query lists the recordcount from all tables.
-- 
---------------------------------------------------------------------*/

Create table #Tables (
	TbName varchar(1000),
	RecCount int NULL

)

Set nocount on

Declare @sql varchar(8000)
Declare @TbName VarChar(1000)
Declare @RecCount int
Declare @ColumnName varchar(100)
Declare @LastUpdated DateTime

Declare Cursor1 Cursor FOR

	Select name from Sysobjects 
	WHERE type = 'u'
	Order by name

Open Cursor1
Fetch Next From Cursor1 Into @TbName
	While @@Fetch_Status = 0
		BEGIN
		SELECT @ColumnName = NULL


		SELECT @ColumnName = (
			Select TOP 1 Column_Name FROM
			INFORMATION_SCHEMA.Columns
			WHERE Table_Name = @TBName
			and Column_Name like '%Updated'
			)



		Select @SQL = 'Insert Into #Tables (TbName, RecCount) Select ''' 
		SELECT @SQL = @SQL + @TbName + ''', Count(*) '
		SELECT @SQL = @SQL + ' FROM '
		SELECT @SQL = @SQL + @TbName

		Exec(@sql)



		Fetch Next From Cursor1 Into @TbName
		END
Close Cursor1
Deallocate Cursor1

Select *
from #Tables

Drop Table #Tables
		
Set nocount off

    SET NOCOUNT ON

    DECLARE @tables     TABLE (TABLE_NAME VARCHAR(256), ROW_COUNT BIGINT)
    DECLARE @row_count  BIGINT
    DECLARE @table      VARCHAR(256) 
    DECLARE @sql        NVARCHAR(4000)

    INSERT INTO @tables (TABLE_NAME, ROW_COUNT)
        SELECT TABLE_NAME,
               -1
          FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_TYPE = 'BASE TABLE'

    WHILE (SELECT COUNT(*) FROM @tables WHERE ROW_COUNT = -1) > 0
        BEGIN
            SELECT @table = TABLE_NAME
              FROM @tables
             WHERE ROW_COUNT = -1

            SET @sql = 'SELECT @row_count = COUNT_BIG(*) FROM ' + @table
            EXEC sp_executesql @sql, N'@row_count BIGINT OUTPUT', @row_count OUTPUT

            UPDATE @tables
               SET ROW_COUNT = @row_count
             WHERE TABLE_NAME = @table
        END

    SELECT * FROM @tables