patrick118
Technical User
I want a list of all databases and there db sizes in 1 query but i get the problem that i need to login into each database to get the right information. In the query below it goes wroing when i try to login another database.
Could you help me get the answer or give another script?
declare @dbname nvarchar(30)
declare @name sysname
declare @dbdesc varchar(600)
set nocount on
--create table filesize
declare @filesize table
(
fileid smallint,
groupid smallint,
groupname nvarchar(50),
size smallint,
maxsize smallint,
status int,
name nvarchar(50),
filename nvarchar(100)
)
declare @filesizedb table
(
dbid smallint,
dbname nvarchar(30)
)
insert into @filesizedb (dbid, dbname)
select dbid, name from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
/*
** Now for each dbid in filesize, build the database sizes
**
*/
declare @curdbid smallint /* the one we're currently working on */
/*
** Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from @filesizedb
while @curdbid IS NOT NULL
begin
set @name = db_name(@curdbid)
use @name
insert into @filesize
select sysfiles.fileid,
sysfilegroups.groupid,
sysfilegroups.groupname,
sysfiles.size,
sysfiles.maxsize,
sysfiles.status,
sysfiles.name,
sysfiles.filename
from sysfiles
LEFT OUTER JOIN sysfilegroups
ON sysfiles.groupid = sysfilegroups.groupid;
/*
** Now get the next, if any dbid.
*/
select @curdbid = min(dbid) from @filesizedb where dbid > @curdbid
end
/*
** Now filesize is complete so we can print out the db info
*/
select *
from @filesize
go
Could you help me get the answer or give another script?
declare @dbname nvarchar(30)
declare @name sysname
declare @dbdesc varchar(600)
set nocount on
--create table filesize
declare @filesize table
(
fileid smallint,
groupid smallint,
groupname nvarchar(50),
size smallint,
maxsize smallint,
status int,
name nvarchar(50),
filename nvarchar(100)
)
declare @filesizedb table
(
dbid smallint,
dbname nvarchar(30)
)
insert into @filesizedb (dbid, dbname)
select dbid, name from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
/*
** Now for each dbid in filesize, build the database sizes
**
*/
declare @curdbid smallint /* the one we're currently working on */
/*
** Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from @filesizedb
while @curdbid IS NOT NULL
begin
set @name = db_name(@curdbid)
use @name
insert into @filesize
select sysfiles.fileid,
sysfilegroups.groupid,
sysfilegroups.groupname,
sysfiles.size,
sysfiles.maxsize,
sysfiles.status,
sysfiles.name,
sysfiles.filename
from sysfiles
LEFT OUTER JOIN sysfilegroups
ON sysfiles.groupid = sysfilegroups.groupid;
/*
** Now get the next, if any dbid.
*/
select @curdbid = min(dbid) from @filesizedb where dbid > @curdbid
end
/*
** Now filesize is complete so we can print out the db info
*/
select *
from @filesize
go
