Get Number of Rows in Every Table

This stored procedure for SQL Server will retrieve a record count of every table in a given database. This is useful for looking at a vendor's (complex) database to see which tables you are actually using.

Source: http://www32.brinkster.com/srisamp/sqlArticles/article_34.htm

Snippet:

BEGIN
	SET NOCOUNT ON

	-- Declare a cursor that gets a list of all the tables
	-- in the database
	DECLARE tablesList CURSOR FOR
		SELECT table_name FROM information_schema.tables
			WHERE table_type = 'BASE TABLE'
			ORDER BY table_name

	-- Scratch variables used in the program
	DECLARE @tableCount INT
	DECLARE @lcTableName VARCHAR(100)
	DECLARE @sqlString NVARCHAR(1000)

	-- Output table that contains all the results
	CREATE TABLE #tableCounts (tableName VARCHAR(100), recordCount INT)
	
	-- Open the cursor and loop through it
	OPEN tablesList
	FETCH NEXT FROM tablesList INTO @lcTableName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		-- From the command to execute against each table and execute
		-- the same
		SET @sqlString = 'SELECT @rowCount = COUNT(*) FROM ' + @lcTableName
		EXEC sp_executesql @sqlString, N'@rowCount INT OUTPUT', @tableCount OUTPUT

		-- Insert the result into the result table	
		INSERT INTO #tableCounts VALUES (@lcTableName, @tableCount)
		FETCH NEXT FROM tablesList INTO @lcTableName
	END
	-- Cleanup the cursor
	CLOSE tablesList
	DEALLOCATE tablesList

	-- Return the result and the temporary tables automatically
	-- goes out of scope	
	SELECT * FROM #tableCounts ORDER BY tableName
END

tags: mssql, query analyzer