The function filerproperty(filename, property) can tell how much space is allocated to a specific file

example:

select name, fileproperty(name,'spaceused') as allocated,

   case fileproperty(name,'IsLogFile')

      when 0 then 'Data'

      else 'LOG'

   end as...

Maybe you're looking for something like this ?

Create Function MaxFromList (@String nvarchar(4000), @delimiter varchar(1) = ',')

returns nvarchar(255)

AS

Begin

   declare @Datalength int

   declare @CurrentPosition int

   declare @NextPosition int

   declare @ThisTable table(Value nvarchar(255))

  ...