Listar ficheros y tamaños de SQL SERVER

Buenas Tardes a todos ,
Os voy a compartir un procedimiento almacenado que lista todos los ficheros de todas las bases de datos que tenemos y nos dirá su tamaño y su nivel de ocupación.

Es relativamente simple utilizando el comando sp_msforeachdb , pero me ha parecido interesante compartirlo con todos vosotros.

Lo dejará en una tabla en tempdb llamada ocupacion que podremos consultar.

SET ANSI_NULLS ONGO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[ocupacions] as
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ('ocupacion') AND type in (N'U'))
 
drop table tempdb.dbo.ocupacion
 
CREATE TABLE tempdb.dbo.ocupacion(
 
[name] [varchar](256) NULL,

[Filename] [varchar](256) NULL,

[size] [int] NULL,

[usedspace] [int] NULL,

[id] [int] NULL,

) ON [PRIMARY]

EXECUTE sp_msforeachdb 'USE  [?]

insert into tempdb.dbo.ocupacion SELECT

DB_NAME() AS [Name],

s.physical_name AS [FileName],

s.size * CONVERT(float,8) AS [Size],

CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) AS [usedspace],

s.file_id AS [ID]

FROM

sys.master_files AS s

WHERE

(s.type = 1 and s.database_id = db_id())

ORDER BY

[ID] ASC'
EXECUTE sp_msforeachdb 'USE  [?]

create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))

insert #tmpspc EXEC (''dbcc showfilestats'')

insert into tempdb.dbo.ocupacion SELECT 
s.name AS [Name],

s.physical_name AS [FileName],

s.size * CONVERT(float,8) AS [Size],

CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],

s.file_id AS [ID]

FROM

sys.filegroups AS g

INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id

ORDER BY

[ID] ASC

DROP TABLE #tmpspc'
GO
EXEC [dbo].[ocupacion]
SELECT * FROM tempdb.dbo.Ocupacion