Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbname sysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regardsHave you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards

Get space used of all databases

Hello,
I need to get space information of all databases and report it. I create one
cursor that enter in every databases and report space used, but it returns
the following error.
"Server: Msg 8114, Level 16, State 5, Line 16
Error converting data type nvarchar to numeric."
If i treat my variables like normal text ('') and use the output generated
it works fine.
I send you part of the script and hope that you can help me
declare @.dbsize dec(15,0),
@.dbnamesysname,
@.sql nvarchar(850)
declare dbid_cur cursor for
select [name]
from master..sysdatabases
where [name] <> 'tempdb' for read only
open dbid_cur
fetch next from dbid_cur into @.dbname
while @.@.fetch_status = 0
begin
set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
+ 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
varchar(20))'
print @.sql
-- sp_executesql @.sql
fetch next from dbid_cur into @.dbname
end
close dbid_cur
deallocate dbid_cur
Thanks and best regards
Have you looked at sp_helpdb? Does it give you what you want?
Keith
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards
|||How about
EXEC sp_msforeachdb 'USE ?; EXEC sp_spaceused'
(Note that the procedure is unsupported.)
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:3001C30F-8E9C-496C-A90C-770CB8D41101@.microsoft.com...
> Hello,
> I need to get space information of all databases and report it. I create
one
> cursor that enter in every databases and report space used, but it returns
> the following error.
> "Server: Msg 8114, Level 16, State 5, Line 16
> Error converting data type nvarchar to numeric."
> If i treat my variables like normal text ('') and use the output generated
> it works fine.
> I send you part of the script and hope that you can help me
> declare @.dbsize dec(15,0),
> @.dbname sysname,
> @.sql nvarchar(850)
> declare dbid_cur cursor for
> select [name]
> from master..sysdatabases
> where [name] <> 'tempdb' for read only
> open dbid_cur
> fetch next from dbid_cur into @.dbname
> while @.@.fetch_status = 0
> begin
> set @.sql = 'use ' + @.dbname + nchar(13) + nchar(10) +
> + 'select ' + @.dbsize + '= cast(sum(convert(dec(15),size))as
> varchar(20))'
> print @.sql
> -- sp_executesql @.sql
> fetch next from dbid_cur into @.dbname
> end
> close dbid_cur
> deallocate dbid_cur
> Thanks and best regards