Technical Article

Row count and space used for all tables in database (update)

,

This procedure works like the sp_spaceused (part of code is taken from it) procedure but this one shows statistics for all tables in the selected database or for all databases (excluding tempdb and model).

Now there are two versions: for SQL Server 2000 and SQL Server 2005.

/*****************************************************************************************************************************************
*
* Author Rafal Skotak
* Purpose This procedure returns a recorset with row count and space used for all tables
* in the specified database (or for all databases except tempdb and model)
* Date 2008.03.05 (version for SQL Server 2005)
*
******************************************************************************************************************************************/

if exists(select * from sys.objects where object_id = object_id('dbo.proc_records_per_database') and type = 'P')
	drop procedure dbo.proc_records_per_database
go

create procedure dbo.proc_records_per_database
	@db_name sysname = NULL
as
begin
	set nocount on

	if @db_name is not null
	begin
		if not exists(select * from master.sys.databases where name = @db_name and database_id not in (2, 3) /* skip tempdb and model */)
		begin
			raiserror('Database does not exist or can not be queried', 16, 1)
			return
		end
	end

	declare @dbs_table table(name sysname not null primary key)

	--------------------------------------------------------------------------------------
	-- prepare databases list

	insert into @dbs_table
	select 
		name 
	from 
		master.sys.databases
	where 
		(@db_name is null or
		(@db_name is not null and name = @db_name)) and
		database_id not in (2, 3)

	create table #temp_table_list
	(
		rec_id int identity(1, 1) not null,
		cat_name sysname not null,
		sch_name sysname not null,
		tab_name sysname not null,
		row_count bigint not null default 0,
		reserved_pages bigint not null default 0,
		reserved bigint not null default 0,
		pages bigint not null default 0,
		data bigint not null default 0,
		used_pages bigint not null default 0,
		used bigint not null default 0,
		index_size bigint not null default 0,
		not_used bigint not null default 0,
		primary key(rec_id)
	)

	declare @cmd varchar(max)
	declare @n_cmd nvarchar(max)
	declare @dbx_name sysname

	--------------------------------------------------------------------------------
	-- get all tables

	while 1 = 1 
	begin
		set @dbx_name = NULL

		select top 1 @dbx_name = [name] from @dbs_table

		if @dbx_name is NULL
			break

		set @cmd = 'insert into #temp_table_list (cat_name, sch_name, tab_name) select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME from [' + @dbx_name + '].INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
		set @n_cmd = cast(@cmd as nvarchar(max))

		exec sp_executesql @n_cmd

		delete from @dbs_table where [name] = @dbx_name
	end

	declare @max_rec_id int
	set @max_rec_id = NULL

	select @max_rec_id = max(rec_id) from #temp_table_list

	if @max_rec_id is NULL
		set @max_rec_id = -1

	declare @counter int
	set @counter = 1

	declare @objname varchar(max)

	while @counter <= @max_rec_id
	begin
		--------------------------------------------------------------------------------
		-- update table usage statistics

		select 
			@cmd = 'use [' + cat_name + ']; dbcc updateusage(0, ''[' + sch_name + '].[' + tab_name + ']'') with no_infomsgs'
		from
			#temp_table_list
		where
			rec_id = @counter

		set @n_cmd = cast(@cmd as nvarchar(max))

		-- print @n_cmd

		exec sp_executesql @n_cmd

		-----------------------------------------------------------------------------------------------
		-- get table stats (based on the code of the procedure sp_spaceused)

		declare @reservedpages_param bigint
		declare @usedpages_param bigint
		declare @pages_param bigint
		declare @index_size_param bigint
		declare @unused_param bigint
		declare @rows_param bigint

		set @reservedpages_param = 0
		set @usedpages_param = 0
		set @pages_param = 0
		set @index_size_param = 0
		set @unused_param = 0
		set @rows_param = 0

		select 
			@cmd = 
			' use [' + cat_name + '];

			declare @id int

			select @id = object_id(''[' + sch_name + '].[' + tab_name + ']'')

			SELECT 
				@reservedpages = sum(reserved_page_count),
				@usedpages = sum(used_page_count),
				@pages = sum(
					CASE
					WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
					ELSE lob_used_page_count + row_overflow_used_page_count
					END
				),
				@rowCount = sum(
					CASE
					WHEN (index_id < 2) THEN row_count
					ELSE 0
					END
				)
			FROM sys.dm_db_partition_stats
			WHERE object_id = @id;

			IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0 
			BEGIN

				SELECT 
					@reservedpages = @reservedpages + sum(reserved_page_count),
					@usedpages = @usedpages + sum(used_page_count)
				FROM sys.dm_db_partition_stats p, sys.internal_tables it
				WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;
			END

			SET @reservedpages = @reservedpages
			SET @index_size = (CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8
			SET @unused = (CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8'	
		from 
			#temp_table_list
		where
			rec_id = @counter

		set @n_cmd = cast(@cmd as nvarchar(max))

		exec sp_executesql 
			@n_cmd,
			@parameters = N'@reservedpages bigint OUTPUT, @usedpages bigint OUTPUT, @pages bigint OUTPUT, @index_size bigint OUTPUT, @unused bigint OUTPUT, @rowCount bigint OUTPUT',
			@reservedpages = @reservedpages_param OUTPUT,
			@usedpages = @usedpages_param OUTPUT,
			@pages = @pages_param OUTPUT,
			@index_size = @index_size_param OUTPUT,
			@unused = @unused_param OUTPUT,
			@rowCount = @rows_param OUTPUT

		update 
			#temp_table_list
		set 
			row_count = @rows_param,
			reserved_pages = @reservedpages_param,
			reserved = @reservedpages_param * 8,
			data = @pages_param * 8,
			index_size = @index_size_param,
			not_used = @unused_param,
			pages = @pages_param,
			used_pages = @usedpages_param,
			used = @usedpages_param * 8
		where
			rec_id = @counter

		set @counter = @counter + 1
	end

	select 
		cat_name, 
		sch_name, 
		tab_name, 
		row_count,
		reserved_pages,
		used_pages,
		pages,
		reserved,
		used,
		data,
		index_size,
		not_used
	from 
		#temp_table_list
	order by 
		cat_name,
		sch_name,
		tab_name

	drop table #temp_table_list
end
go

-- example A:

exec dbo.proc_records_per_database 'AdventureWorks'

-- example A:

exec dbo.proc_records_per_database


/*****************************************************************************************************************************************
*
* Author Rafal Skotak
* Purpose This procedure returns a recorset with row count and space used for all tables
* in the specified database (or for all databases except tempdb and model)
* This is the version for SQL Server 2000
* Date 2008.04.21
*
******************************************************************************************************************************************/


if exists(select * from sysobjects where id = object_id('dbo.proc_records_per_database') and type = 'P')
	drop procedure dbo.proc_records_per_database
go

create procedure dbo.proc_records_per_database
	@db_name sysname = NULL
as
begin
	set nocount on

	if @db_name is not null
	begin
		if not exists(select * from master.dbo.sysdatabases where name = @db_name and dbid not in (2, 3) /* skip tempdb and model */)
		begin
			raiserror('Database does not exist or can not be queried', 16, 1)
			return
		end
	end

	declare @dbs_table table(name sysname not null primary key)

	--------------------------------------------------------------------------------------
	-- prepare databases list

	insert into @dbs_table
	select 
		name 
	from 
		master.dbo.sysdatabases
	where 
		(@db_name is null or
		(@db_name is not null and name = @db_name)) and
		dbid not in (2, 3)

	create table #temp_table_list
	(
		rec_id int identity(1, 1) not null,
		cat_name sysname not null,
		sch_name sysname not null,
		tab_name sysname not null,
		row_count bigint not null default 0,
		reserved_pages bigint not null default 0,
		reserved bigint not null default 0,
		pages bigint not null default 0,
		data bigint not null default 0,
		used_pages bigint not null default 0,    
		used bigint not null default 0,
		index_size bigint not null default 0,
		not_used bigint not null default 0,
		primary key(rec_id)
	)

	declare @cmd varchar(4000)
	declare @n_cmd nvarchar(4000)
	declare @dbx_name sysname

	--------------------------------------------------------------------------------
	-- get all tables

	while 1 = 1 
	begin
		set @dbx_name = NULL

		select top 1 @dbx_name = [name] from @dbs_table

		if @dbx_name is NULL
			break

		set @cmd = 'insert into #temp_table_list (cat_name, sch_name, tab_name) select ''' + @dbx_name + ''', '''', name from [' + @dbx_name + '].dbo.sysobjects where type = ''U'''

		set @n_cmd = cast(@cmd as nvarchar(4000))

		exec sp_executesql @n_cmd

		delete from @dbs_table where [name] = @dbx_name
	end

	declare @max_rec_id int
	set @max_rec_id = NULL

	select @max_rec_id = max(rec_id) from #temp_table_list

	if @max_rec_id is NULL
		set @max_rec_id = -1

	declare @counter int
	set @counter = 1

	declare @objname varchar(4000)

	while @counter <= @max_rec_id
	begin
		--------------------------------------------------------------------------------
		-- update table usage statistics

		select 
			@cmd = 'use [' + cat_name + ']; dbcc updateusage(0, ''[' + tab_name + ']'') with no_infomsgs'
		from
			#temp_table_list
		where
			rec_id = @counter

		set @n_cmd = cast(@cmd as nvarchar(4000))

		-- print @n_cmd

		exec sp_executesql @n_cmd

		-----------------------------------------------------------------------------------------------
		-- get table stats (based on the code of the procedure sp_spaceused)

		declare @reservedpages_param bigint
		declare @usedpages_param bigint
		declare @pages_param bigint
		declare @index_size_param bigint
		declare @unused_param bigint
		declare @rows_param bigint

		set @reservedpages_param = 0
		set @usedpages_param = 0
		set @pages_param = 0
		set @index_size_param = 0
		set @unused_param = 0
		set @rows_param = 0

		select 
			@cmd = 
'use [' + cat_name + '];
declare @id int
select @id = object_id(''[' + tab_name + ']'')
select @reservedpages = sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id 
select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id
select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id
select @index_size = (sum(used) - @pages) from sysindexes where indid in (0, 1, 255) and id = @id
select @usedpages = @index_size + @pages
set @index_size = @index_size * 8
select @unused = (@reservedpages - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id)) * 8
select @rowCount = rows from sysindexes where indid < 2 and id = @id'
		from 
			#temp_table_list
		where
			rec_id = @counter

		set @n_cmd = cast(@cmd as nvarchar(4000))

		-- print @n_cmd

		exec sp_executesql 
			@n_cmd,
			@parameters = N'@reservedpages bigint OUTPUT, @usedpages bigint OUTPUT, @pages bigint OUTPUT, @index_size bigint OUTPUT, @unused bigint OUTPUT, @rowCount bigint OUTPUT',
			@reservedpages = @reservedpages_param OUTPUT,
			@usedpages = @usedpages_param OUTPUT,
			@pages = @pages_param OUTPUT,
			@index_size = @index_size_param OUTPUT,
			@unused = @unused_param OUTPUT,
			@rowCount = @rows_param OUTPUT

		update 
			#temp_table_list
		set 
			row_count = @rows_param,
			reserved_pages = @reservedpages_param,
			reserved = @reservedpages_param * 8,
			data = @pages_param * 8,
			index_size = @index_size_param,
			not_used = @unused_param,
			pages = @pages_param,
			used_pages = @usedpages_param,
			used = @usedpages_param * 8
		where
			rec_id = @counter

		set @counter = @counter + 1
	end

	select 
		cat_name, 
		tab_name, 
		row_count,
		reserved_pages,
		used_pages,
		pages as data_pages,
		reserved,
		used,
		data,
		index_size,
		not_used
	from 
		#temp_table_list
	order by 
		cat_name, 
		tab_name

	drop table #temp_table_list
end
go

-- example:

exec dbo.proc_records_per_database 'AdventureWorks2000'

exec dbo.proc_records_per_database 'Northwind'

Rate

4.27 (11)

You rated this post out of 5. Change rating

Share

Share

Rate

4.27 (11)

You rated this post out of 5. Change rating