CREATE procedure usp_get_mdf_size
@dbname varchar(100)
AS
SET NOCOUNT ON;
DECLARE @sql_statement AS nvarchar(4000)
IF @dbname IS NULL OR @dbname = ''
BEGIN
SET @dbname = DB_NAME()
END
IF @dbname = 'ALL'
BEGIN
set @sql_statement =
'
create TABLE #tab1 (
DBname VARCHAR(500)
,filegroupname VARCHAR(500)
,logical_filename VARCHAR(500)
,physical_filename VARCHAR(500)
,max_size_mb DECIMAL
,current_size_mb DECIMAL
,Space_Used_MB DECIMAL
,Available_Space_MB DECIMAL
);
DECLARE @sql_statement AS nvarchar(4000)
set @sql_statement =
' +CHAR(39) + '
USE [?]
IF ' +CHAR(39) +CHAR(39)+ '?' +CHAR(39) +CHAR(39) + ' NOT IN ('+CHAR(39) +CHAR(39) + 'master'+CHAR(39) +CHAR(39) + ',' +CHAR(39) +CHAR(39)+ 'tempDB'+CHAR(39) +CHAR(39) + ',' +CHAR(39) + CHAR(39)+ 'model'+ CHAR(39) +CHAR(39) + ',' +CHAR(39) +CHAR(39)+ 'msdb' +CHAR(39) +CHAR(39) + ')
insert #tab1 (DBname, filegroupname ,logical_filename, physical_filename, max_size_mb, current_size_mb ,Space_Used_MB, Available_Space_MB)
SELECT
db_name() as dbname
,b.groupname AS ' + char(39) + char(39) + ' File_Group' + char(39) + char(39) + Char(13) +'
,Name as logical_name
,[Filename]
,CONVERT(DECIMAL(15, 2), ROUND(a.maxsize / 128.000, 2)) [MAX_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(a.Size / 128.000, 2)) [CURRENT_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(FILEPROPERTY(a.Name, '+ CHAR(39) + char(39) + 'SpaceUsed'+ CHAR(39) + char(39) + ') / 128.000, 2)) AS [Space_Used_MB]
,CONVERT(DECIMAL(15, 2), ROUND((a.maxsize - FILEPROPERTY(a.Name, '+CHAR(39)+ char(39) + 'SpaceUsed'+CHAR(39) + char(39) + ')) / 128.000, 2)) AS [Available_Space_MB]
FROM dbo.sysfiles a(NOLOCK)
JOIN sysfilegroups b(NOLOCK) ON a.groupid = b.groupid
' + CHAR(39) +'
EXEC sp_MSforeachdb @sql_statement
select DBname,case max_size_mb when 0 then '+ char(39) + '제한없음'+ char(39) +' else CAST (max_size_mb AS VARCHAR(50)) END [MAX_SIZE_MB]
, current_size_mb
,Space_Used_MB
, CASE WHEN Available_Space_MB < 1 THEN '+ char(39) + '제한없음'+ char(39) +' else CAST (Available_Space_MB AS VARCHAR(50)) END Available_Space_MB
, case max_size_mb when 0 then -1 else CONVERT(DECIMAL(15, 2), ROUND((Available_Space_MB / max_size_mb) * 100, 2)) END Available_percent
, filegroupname
,logical_filename
, physical_filename
from #tab1
WHERE DBname NOT IN(select name from sys.databases where is_read_only=1)
order BY Available_percent
drop table #tab1
'
END
ELSE
BEGIN
set @sql_statement =
'
USE ' + @dbname + Char(13) +'
SELECT
db_name() as dbname
,case a.maxsize when -1 then '+ char(39) + '제한없음'+ char(39) +' else CAST (CONVERT(DECIMAL(15, 2), ROUND(a.maxsize / 128.000, 2)) AS VARCHAR(50)) END [MAX_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(a.Size / 128.000, 2)) [CURRENT_SIZE_MB]
,CONVERT(DECIMAL(15, 2), ROUND(FILEPROPERTY(a.Name, ' + char(39) + 'SpaceUsed' + char(39) + ') / 128.000, 2)) AS [Space_Used_MB]
,CASE WHEN a.maxsize < 1 THEN '+ char(39) + '제한없음'+ char(39) +' else CAST(CONVERT(DECIMAL(15, 2), ROUND((a.maxsize - FILEPROPERTY(a.Name, '+CHAR(39) + 'SpaceUsed'+CHAR(39) + ')) / 128.000, 2)) AS VARCHAR(50)) END AS [Available_Space_MB]
,CASE WHEN a.maxsize < 1 THEN 100 ELSE CONVERT(DECIMAL(15, 2), ROUND((((a.MAXSize - FILEPROPERTY(a.Name, '+CHAR(39) + 'SpaceUsed'+ CHAR(39) + ')) / 128.000) / (a.maxsize / 128.000)) * 100, 2)) END AS Available_percent
,b.groupname AS ' + char(39) + ' File_Group' + char(39) + Char(13) +'
,Name as logical_name
,[Filename]
FROM dbo.sysfiles a(NOLOCK)
JOIN sysfilegroups b(NOLOCK) ON a.groupid = b.groupid
'
END
EXECUTE sp_executesql @sql_statement
--PRINT @sql_statement
2023. 9. 11. 08:59
카테고리 없음