In this post I’m actually just going to repeat what I answered to a question on Experts Exchange.
The OP was having an issue getting a record count of all tables in his database. It was working through the Management Studio, using the undocumented but well-known sp_MSforeachtable stored procedure.
exec sp_MSforeachtable 'select ''?'' TableName, count(*) Cnt from ?';
However, the OP needed the list of record counts inside an Integration Services package. And this didn’t work out as expected, because sp_MSforeachtable uses a temporary table, something that SSIS does not like.
So I proposed to use a script similar to the following, possibly in a stored procedure.
/* DESCRIPTION: Returns a list of record counts for each table in the database AUTHOR: Valentino Vranken CREATED: 2011/05/21 VERSION: 1.0 COPIED FROM: http://blog.hoegaerden.be */ declare @tempTable table ( TableSchema nvarchar(256), TableName nvarchar(256), Cnt bigint ); declare @sql nvarchar(4000); declare @tableSchema nvarchar(256); declare @tableName nvarchar(256); declare @columnName sysname; declare @cnt bigint; declare tableCursor cursor for select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'; open tableCursor; fetch next from tableCursor into @tableSchema, @tableName; while @@FETCH_STATUS = 0 begin set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + ']'; exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output; insert into @tempTable select @tableSchema, @tableName, @cnt; fetch next from tableCursor into @tableSchema, @tableName; end; close tableCursor; deallocate tableCursor; select * from @tempTable;
Yeah, I know, cursors are bad… But hey, at least it works! Just don’t schedule it for execution every five seconds.
I seem to be repeating myself. I wrote something similar when I posted the script about finding empty columns many months ago.
The most important part of the script is the following:
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE';
This is using one of the information schema views to retrieve a list of tables in the database. The INFORMATION_SCHEMA.TABLES view contains a list of tables and views, so the where clause is needed to filter out the views. INFORMATION_SCHEMA.TABLES is just one of many interesting views available in SQL Server, and should always be used in favor of the sys tables.
What does the output look like? Here’s part of the output when executed on the AdventureWorks2008R2 database: