my code stock.com

tei219

check_alldatabase_sp_spaceused
by tei219

Snippet options

Download: Download snippet as check_alldatabase_sp_spaceused.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

create table ##check_alldatabase_sp_spaceused (
	dbname nvarchar(128),
	[name] nvarchar(128),
	[rows] char(11),
	reserved_inKB varchar(18),
	data_inKB varchar(18),
	index_size_inKB varchar(18),
	unused_inKB varchar(18)
)

if (CONVERT(varbinary,@@microsoftversion) > 0x09000000)
begin
exec sp_MSforeachdb @replacechar = '*', @command1 = '
use [*]
if ''*'' not in (''tempdb'')
begin
	create table ##result (
		[name] nvarchar(128),
		[rows] char(11),
		reserved varchar(18),
		data varchar(18),
		index_size varchar(18),
		unused varchar(18)
	)
	exec sp_MSforeachtable @command1 = ''insert ##result exec sp_spaceused [?]''
	insert into ##check_alldatabase_sp_spaceused
	select 
		db_name(),
		[name],
		[rows],
		replace(reserved,'' KB'',''''),
		replace(data,'' KB'',''''),
		replace(index_size,'' KB'',''''),
		replace(unused,'' KB'','''') 
	from 
		##result
	drop table ##result
end
'
end
else
begin
	declare @@name varchar(128)
	declare @@cmd varchar(2000)
	declare cur cursor forward_only for select name from master.dbo.sysdatabases with(nolock)
	open cur
	fetch next from cur into @@name
	while @@fetch_status = 0
	begin
		if @@name not in ('tempdb')
		begin
		set @@cmd = 'use ['+@@name+']
		create table ##result (
			[name] nvarchar(128),
			[rows] char(11),
			reserved varchar(18),
			data varchar(18),
			index_size varchar(18),
			unused varchar(18)
		)
		exec sp_MSforeachtable @command1 = ''insert ##result exec sp_spaceused [?]''
		insert into ##check_alldatabase_sp_spaceused
		select 
			db_name(),
			[name],
			[rows],
			replace(reserved,'' KB'',''''),
			replace(data,'' KB'',''''),
			replace(index_size,'' KB'',''''),
			replace(unused,'' KB'','''') 
		from 
			##result
		drop table ##result
		'
		exec (@@cmd)
		end 
		fetch next from cur into @@name
	end
	close cur
	deallocate cur
end

select * from ##check_alldatabase_sp_spaceused

drop table ##check_alldatabase_sp_spaceused

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.