Msg 102, Level 15, State 1, Line 1 “-”附近有语法错误。

CanuxCheng

SET  NOCOUNT ON;
SET  ANSI_NULLS ON;
SET  QUOTED_IDENTIFIER ON;

DECLARE
  @SQL     NVARCHAR(4000),
  @dbname  sysname;
  
declare  
  @datatab table
          (name    sysname,
           dbsize  float,
           dbused  float,
           logsize float,
           logused float);

declare
  DBCursor      cursor for
                select name from sys.databases order by database_id;

open dbcursor;
fetch NEXT FROM dbcursor INTO @dbname;

while @@FETCH_STATUS = 0
BEGIN
  set @SQL='use ' + @dbname + '; SELECT ''' +  @dbname + ''' as dbname' +
       ', (SELECT SUM(CAST(size AS FLOAT)) / 128 FROM sys.database_files WHERE  type IN (0, 2, 4)) dbsize' +
       ', SUM(CAST(a.total_pages AS FLOAT)) / 128 reservedsize' +
       ', (SELECT SUM(CAST(size AS FLOAT)) / 128 FROM sys.database_files WHERE  type IN (1, 3)) logsize' +
       ', (select sum(cast(fileproperty(name, ''SpaceUsed'') as float))/128 from sys.database_files where type in (1,3)) logUsedMB' +
       ' FROM ' + @dbname + '.sys.partitions p' +
       ' INNER JOIN ' + @dbname + '.sys.allocation_units a ON p.partition_id = a.container_id' +
       ' LEFT OUTER JOIN ' + @dbname + '.sys.internal_tables it ON p.object_id = it.object_id';
      
  insert into @datatab
  execute(@SQL);
  --print @SQL
  fetch NEXT FROM dbcursor INTO @dbname;
end;

CLOSE dbcursor;
DEALLOCATE dbcursor;   
   
select  name
      , dbsize
      , dbused
      , round(dbused / dbsize *100, 2) dbpercent
      , logsize
      , logused
      ,round(logused / logsize *100, 2) logpercent
from @datatab d order by name;