Script to gather informations on databases

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hisdatabases]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hisdatabases]
GO

CREATE TABLE [dbo].[hisdatabases] (
[Serveur] [varchar] (15)  NOT NULL ,
[DATABASE_NAME] [sysname] NOT NULL ,
[SIZE] [varchar] (50)  NULL ,
[RUN_DT] [datetime] NOT NULL ,
[TYPE] [varchar] (4) NOT NULL ,
[FREE] [varchar] (50) NULL ,
[PourcentLibre] [varchar] (50) NULL ,
[lname] [varchar] (40) NULL ,
[pname] [varchar] (255) NULL 
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[databases]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[databases]
GO

CREATE TABLE [dbo].[databases] (
[Serveur] [varchar] (15)  NOT NULL ,
[DATABASE_NAME] [sysname] NOT NULL ,
[SIZE] [varchar] (50)  NULL ,
[RUN_DT] [datetime] NOT NULL ,
[TYPE] [varchar] (4) NOT NULL ,
[FREE] [varchar] (50) NULL ,
[PourcentLibre] [varchar] (50) NULL ,
[lname] [varchar] (40) NULL ,
[pname] [varchar] (255) NULL 
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_databases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_databases]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc sp_databases

as
/*          Procedure for 8.0 server 
Sert à récupérer dans une table ( temporaire ) les infos 
sur la taille des bases du serveur en question, excepté les base systemes */
set nocount on
declare 
@name sysname,
@size dec,
@unallocated_space varchar(20),
@dbsize int,
@logsize int,
@bytesperpage int,
@pagesperMB int,
@somme varchar(50),
@taille varchar(50),
@lpname varchar(20),
@pourcent dec(15,2),
@compte int,
@SQL nvarchar(600)
/* Use temporary table to sum up database size w/o using group by */create table #databases (
DATABASE_NAME sysname NOT NULL,
size int NOT NULL,
name varchar(255),
Free int )
declare c1 cursor for select name from master.dbo.sysdatabases where has_dbaccess(name) = 1 and dbid > 4 -- Only look at databases to which we have access
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL = 'insert into #databases
select N'''+ @name + ''',size,''Data'',(select sum(convert(dec(15),reserved))
from '+quotename(@name)+'.dbo.sysindexes
where indid in (0, 1, 255)) from '+ QuoteName(@name) + '.dbo.sysfiles where  (status & 64 = 0)'
print @SQL
/* Insert row for each database on data */execute (@SQL)

select @SQL = 'insert into #databases
select N'''+ @name + ''',size,''Log'',0 from '+ QuoteName(@name) + '.dbo.sysfiles where (status & 64 <> 0)'

print @SQL
/* Insert row for each database on log */execute (@SQL)

fetch  c1 into @name
end
deallocate c1
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage


select @compte = (select count(*) from databases)
print @compte
if @compte > 1 
BEGIN 
insert into hisdatabases select * from databases
delete from databases
END
insert into databases(Serveur,DATABASE_NAME,SIZE,RUN_DT,TYPE,FREE,PourcentLibre) select @@SERVERNAME,DATABASE_NAME,DATABASE_SIZE_en_MO = str(convert(dec(15), SIZE)*(select low from master.dbo.spt_values where type = 'E' and number = 1) / 1048576,10,2),/* Convert from 8192 byte pages to MO, if so, verify on spt_value*/RUN_DT=GETDATE(),name,'unallocated space' =
(ltrim(str((convert(dec(15), SIZE) -  free) / @pagesperMB,15,2))),0 from #databases order by 1


update databases set free=0 where type='Log'

declare freec cursor for select DATABASE_NAME,size,free from databases where type='Data'
open freec
fetch freec into @name,@taille,@somme

while @@fetch_status >= 0
BEGIN
--print 'Taille: '+@taille+' somme:'+@somme
select @pourcent= CONVERT(dec(15,2),(100 / CONVERT(dec(15,2),@taille)* ABS(CONVERT(dec(15,2),@somme))))
--PRINT @pourcent
update databases set PourcentLibre=@pourcent where DATABASE_NAME =@name and Type='Data'

fetch freec into @name,@taille,@somme
end
deallocate freec

declare lpname cursor for select database_name from databases
open lpname
fetch lpname into @lpname
while @@fetch_status >= 0
BEGIN
select @sql='update databases set Lname=(select name from '+@lpname+'..sysfiles where groupid=1) 
where database_name='''+@lpname+''''+'and type='''+'Data'+''''
print @SQL
execute (@SQL)
select @sql='update databases set Lname=(select name from '+@lpname+'..sysfiles where groupid=0) 
where database_name='''+@lpname+''''+'and type='''+'Log'+''''
print @SQL
execute (@SQL)
select @sql='update databases set Pname=(select filename from '+@lpname+'..sysfiles where groupid=1 )
where database_name='''+@lpname+''''+'and type='''+'Data'+''''
print @SQL
execute (@SQL)
select @sql='update databases set Pname=(select filename from '+@lpname+'..sysfiles where groupid=0 )
where database_name='''+@lpname+''''+'and type='''+'Log'+''''
print @SQL
execute (@SQL)
fetch lpname into @lpname
end
deallocate lpname


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO