Blog ala Vidar

SQL, AppFrame and other cool technologies

Scripts – COUNT

Records In All Tables

DECLARE @DBObjectID AS NVARCHAR(128)
DECLARE @NumRows AS INT
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name, CAST(NULL AS INT) AS NumRows
	INTO #tables
	FROM sys.sysobjects
	WHERE name LIKE 'atbl_%'
	  AND xtype = 'U'

WHILE EXISTS (SELECT * FROM #tables WHERE NumRows IS NULL)
BEGIN
	SELECT TOP 1
	  @DBObjectID = name
		FROM #tables
		WHERE NumRows IS NULL

	SET @SQL = 'SELECT @NumRows = COUNT(*) FROM [' + @DBObjectID + ']'
	EXEC sp_executesql @SQL, N'@NumRows INT OUTPUT', @NumRows = @NumRows OUTPUT

	UPDATE #tables
		SET NumRows = @NumRows
		WHERE name = @DBObjectID
END

SELECT * FROM #tables ORDER BY name

DROP TABLE #tables
<span>%d</span> bloggers like this: