script to shows all dbs on all servers.

I am writing a script that would give me databases of all servers. I also
need to know whats the database size fo each database on each server. The
way i get teh first one working was set up linked server to all databases
that i need the information from. Then for each server get the databases
from sysdatabases. I run into problem when i need to know the database
size. sp_spaceused returns 2 rows for a database is there any procedure
that i can use in msforeachdb to get the database size. Or is there nay
other means to do this. any help will be greatly appreciated.
TIA
this is the text for my existing proc.
CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
AS
DECLARE @.ServerName varchar(100),
@.return int,
@.sql nvarchar(1000)
SET NOCOUNT ON
if exists (select * from master.dbo.sysobjects where name = 'Details')
BEGIN
truncate table master..Details
end
else
begin
Create Table Master..Details
( ServerName Char(100),
DatabaseName Char(100))
end
DECLARE server_cursor CURSOR
FOR
SELECT srvname FROM master..sysservers
where srvname not in ('repl_distributor')
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @.ServerName
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName)
+']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
--select @.sql
exec sp_executesql @.sql
FETCH NEXT FROM server_cursor INTO @.ServerName
END
DEALLOCATE server_cursor
SELECT * from master..Details
SET NOCOUNT OFF
GO
Message posted via http://www.webservertalk.comKilobytes of all files used by the current database (data and log files):
SELECT SUM([size]) * 8 FROM dbo.sysfiles
sysfiles.size is the number of pages used by the db; each page is 8 kb; so
size * 8 = kb. multiply by 1024 to get bytes, or divide by 1024 to get mb,
etc.
"ishaan99 via webservertalk.com" wrote:

> I am writing a script that would give me databases of all servers. I also
> need to know whats the database size fo each database on each server. The
> way i get teh first one working was set up linked server to all databases
> that i need the information from. Then for each server get the databases
> from sysdatabases. I run into problem when i need to know the database
> size. sp_spaceused returns 2 rows for a database is there any procedure
> that i can use in msforeachdb to get the database size. Or is there nay
> other means to do this. any help will be greatly appreciated.
> TIA
> this is the text for my existing proc.
> CREATE PROCEDURE dbo.PR_ALLDBSOFALLSERVERS
>
> AS
> DECLARE @.ServerName varchar(100),
> @.return int,
> @.sql nvarchar(1000)
> SET NOCOUNT ON
> if exists (select * from master.dbo.sysobjects where name = 'Details')
> BEGIN
> truncate table master..Details
> end
> else
> begin
>
> Create Table Master..Details
> ( ServerName Char(100),
> DatabaseName Char(100))
> end
> DECLARE server_cursor CURSOR
> FOR
> SELECT srvname FROM master..sysservers
> where srvname not in ('repl_distributor')
> OPEN server_cursor
> FETCH NEXT FROM server_cursor INTO @.ServerName
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.sql = 'INSERT INTO master..Details SELECT ''[' + rtrim(@.ServerName
)
> +']'', name from [' + rtrim(@.Servername)+'].master.dbo.sysdatabases'
> --select @.sql
> exec sp_executesql @.sql
> FETCH NEXT FROM server_cursor INTO @.ServerName
> END
> DEALLOCATE server_cursor
> SELECT * from master..Details
> SET NOCOUNT OFF
> GO
> --
> Message posted via http://www.webservertalk.com
>sql