July 12, 2007 at 2:57 am
Hello,
I've created 1 SP to get back data/log files information for each database of one server. My problem is that it's all right just for only one database. My stored procedure can't loop on all databases. Can you help me, I have no idea!!!!! You can try if you want
SP1 :
ALTER PROCEDURE [dbo].[Inventaire_database] @dbname varchar(100) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(1000)
DECLARE cu CURSOR
FOR
select name as 'nom base' from sys.databases
-- SELECT s.name + '.' + t.name
-- FROM sys.tables AS t
-- JOIN sys.schemas AS s ON s.schema_id = t.schema_id;
OPEN cu;
FETCH NEXT FROM cu INTO @dbname;
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec Inventory_dba3 @dbname= @dbname
--RETURN @dbname
FETCH NEXT FROM cu INTO @dbname;
END;
CLOSE cu;
DEALLOCATE cu;
END
SP 2 :
ALTER PROCEDURE [dbo].[Inventory_dba3] @dbname varchar(100)
AS
BEGIN
declare @dbsize bigint
declare @logsize bigint
declare @database_size_mb float
declare @unallocated_space_mb float
declare @reserved_mb float
declare @data_mb float
declare @index_mb float
declare @unused_mb float
declare @reservedpages bigint
declare @pages bigint
declare @usedpages bigint
declare @sql varchar(1000)
--declare @table22 sysname
--select @table22= @dbname+ '.dbo.sysfiles'
--set @table22 = @dbname+ '.dbo.sysfiles'
exec ('Use '+ @dbname +';')
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
,@logsize = sum(convert(bigint,case when status & 64 != 0 then size else 0 end))
from @dbname.dbo.sysfiles
exec ('Use '+ @dbname +';')
select @reservedpages = sum(a.total_pages)
,@usedpages = sum(a.used_pages)
,@pages = sum(CASE
WHEN it.internal_type IN (202,204) THEN 0
WHEN a.type != 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
from sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select @database_size_mb = (convert(dec (15,2),@dbsize) + convert(dec(15,2),@logsize)) * 8192 / 1048576
select @unallocated_space_mb =(case
when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576
else 0
end)
select @reserved_mb = @reservedpages * 8192 / 1048576.0
select @data_mb = @pages * 8192 / 1048576.0
select @index_mb = (@usedpages - @pages) * 8192 / 1048576.0
select @unused_mb = (@reservedpages - @usedpages) * 8192 / 1048576.0
insert into DBATECH.dbo.TEST select @@servername as 'serveur',@@version as 'version',@dbname as 'nom base', @database_size_mb as 'database_size'
, cast (@unallocated_space_mb*100.0/@database_size_mb as decimal(10,2))as 'unallocated'
, cast (@reserved_mb*100/@database_size_mb as decimal(10,2))as 'reserved'
, cast(@data_mb*100/@database_size_mb as decimal(10,2))as 'data'
, cast(@index_mb*100/@database_size_mb as decimal(10,2)) as 'index_1'
, cast(@unused_mb*100/@database_size_mb as decimal(10,2))as 'unused'
, cast((@database_size_mb - @unallocated_space_mb - @reserved_mb)*100/@database_size_mb as decimal (10,2))as 'TransactionLog'
, (@database_size_mb - @unallocated_space_mb - @reserved_mb) as 'TransactionLogSize'
, cast((@unallocated_space_mb + @reserved_mb)*100/@database_size_mb as decimal (10,2)) as 'DataFiles' ;
print @@servername
print @@version
print @dbname
print @database_size_mb
print @unallocated_space_mb*100.0/@database_size_mb
print @reserved_mb*100/@database_size_mb
print @data_mb*100/@database_size_mb
print @index_mb*100/@database_size_mb
print @unused_mb*100/@database_size_mb
print (@database_size_mb - @unallocated_space_mb - @reserved_mb)*100/@database_size_mb
print ( @database_size_mb - @unallocated_space_mb - @reserved_mb)
print ( @unallocated_space_mb + @reserved_mb)*100/@database_size_mb
end
I get back all the name of databases but the files sizes are the same for all !!!!!
Please help me!!!!
PS : I have the same results with the stored procedure exec sp_MSforeachdb 'exec Inventory_dba4'.
thank's a lot
Gaëlle
July 12, 2007 at 4:39 am
There are various ways to do something like that, but my favourite way is using master..sp_msforeachdb. It's the easiest way to execute an sql command in each database. Google for sp_msforeachdb ad you will find lot's of examples.
Markus
[font="Verdana"]Markus Bohse[/font]
July 12, 2007 at 7:16 am
I dont't see how I can use this procedure in my code...
I've tried exec sp_MSforeachdb @command1='exec mySP' but it's the same result.
Can you explain me ?
Thank's
Gaëlle
July 12, 2007 at 12:13 pm
please refer to this posting - i think it will explain the structure you need to use for what you want to do.... i suspect it means nesting your stored procedures three deep.
(if someone could tell me how to activate a link inside these boards....?)
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=380840
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply