Hi,
Anyone could help me to correct the script on SQL Server 2000? Thanks in advance
--------------------
USE [Data_ADM]
GO
/****** Object: StoredProcedure [dbo].[CTRL_Spaceused] Script Date: 11/02/2021 10:59:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CTRL_Spaceused]
AS
BEGIN
/*
Title : CTRL_Spaceused
*/
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
DECLARE @drive varchar(100);
CREATE TABLE #Temp1
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
DECLARE @Rundate datetime = getdate()
EXEC sp_MSforeachdb ' USE [?];
INSERT INTO #Temp1
SELECT
DB_NAME() [Database] ,
''FileGroupName''= CASE
WHEN fg.name IS NULL THEN MF.type_desc
ELSE fg.name
END ,
MF.size / 128.0 ,
MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ),
MF.Max_size,
MF.physical_name
FROM sys.database_files MF
LEFT JOIN sys.filegroups fg
ON MF.data_space_id = fg.data_space_id
LEFT JOIN sys.data_spaces ds
ON ds.data_space_id = MF.data_space_id
WHERE MF.Type in (1,0)
'
DECLARE @temp2 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
INSERT INTO @temp2
SELECT
[Database] ,
[FileGroupName] ,
Sum([File Size]) as [File Size] ,
Sum([File Used Space]) AS [File Used Space],
[Max_size],
[physical_name]
FROM #temp1
WHERE [Database] NOT IN ('distribution', 'master', 'model', 'msdb')
GROUP BY
[Database] ,
[FileGroupName],
[Max_size],
[physical_name]
DECLARE @temp4 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL
)
INSERT INTO @temp4
SELECT
[Database] ,
[FileGroupName] ,
[File Size] as [File Size_MB] ,
[File Used Space] AS [File Used Space_MB],
100-(CONVERT(decimal(5,2),(CONVERT(DECIMAL(10, 2),[File Used Space])/CONVERT(DECIMAL(10, 2),[File Size])*100))) AS [% Free Space],
"Autogrowth" = CASE
WHEN (Max_size) <0 THEN 'File will grow until the disk is full'
WHEN (Max_size) = 0 THEN 'No growth is allowed'
ELSE cast(Max_size/128 as varchar (255))
END,
LEFT([physical_name] ,1) AS Drive_Letter
FROM @Temp2
DROP TABLE #Temp1
DECLARE @temp3 TABLE
(
[Drive] varchar(100) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)
INSERT INTO @temp3
SELECT distinct LEFT ((volume_mount_point),1),
total_bytes/1048576 as Size_in_MB,
available_bytes/1048576 as Free_in_MB,
available_bytes/1048576 as Total_Available_MB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1
DECLARE @Temp5 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)
INSERT INTO @Temp5
SELECT T4.*,T3.[Total MB] ,T3.[Total Free MB] ,T3.[Total Available MB] ,T3.[Percent Free] FROM @temp3 T3
INNER JOIN @temp4 T4
ON T3.Drive=T4.Drive_Letter
DECLARE @Temp6 TABLE
(
[Msg] varchar(256) NOT NULL,
[Status] varchar(1000) NULL
)
INSERT INTO @Temp6
SELECT [Database] +' '+[FileGroupName],
"STATUS" = CASE
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) >10 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 10) THEN '0'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) > 10 THEN '0'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) < 90 THEN '0'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <10 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) > 5 OR CONVERT(DECIMAL(5,2),[Percent
Free]) > 5) THEN '1'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),LEFT([Percent Free],Charindex('%',[Percent Free])-1)) >5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 THEN '1'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 90 AND ([File Used Space_MB]/[Autogrowth]*100)< 95 THEN '1'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <5 THEN '2'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) < '5' THEN '2'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 95 THEN '2'
END
FROM @Temp5
SELECT top (1) [Status] as State,[Msg] FROM @temp6
ORDER BY STATUS DESC
END
Result :
Server: Msg 139, Level 15, State 1, Procedure Check_Spaceused, Line 29
Cannot assign a default value to a local variable.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 125
Line 125: Incorrect syntax near 'APPLY'.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 174
Line 174: Incorrect syntax near '('.
