???????????????????????????????????????·???????SQL??????￡?

View Code

IF NOT EXISTS ( SELECT  *
                FROM    sys.tables
                WHERE   name = 'tablespaceinfo' )
    BEGIN
        CREATE TABLE tablespaceinfo --????????洢??
            (
              Table_Name VARCHAR(50) ??
              Rows_Count INT ??
              reserved INT ??
              datainfo INT ??
              index_size INT ??
              unused INT
            )
    END
DELETE  FROM tablespaceinfo
 --????????

CREATE TABLE #temp --????????洢??
    (
      nameinfo VARCHAR(50) ??
      rowsinfo INT ??
      reserved VARCHAR(20) ??
      datainfo VARCHAR(20) ??
      index_size VARCHAR(20) ??
      unused VARCHAR(20)
    )
DECLARE @tablename VARCHAR(255)
 --??????

DECLARE @cmdsql NVARCHAR(500)

DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name
    FROM    [INFORMATION_SCHEMA].[TABLES]
    WHERE   TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME <> 'tablespaceinfo'

OPEN Info_cursor

FETCH NEXT FROM Info_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
            + ''''
        EXECUTE sp_executesql @cmdsql
        FETCH NEXT FROM Info_cursor
INTO @tablename
    END

CLOSE Info_cursor
DEALLOCATE Info_cursor
GO


--itlearner??????????????
--sp_spaceused @updateusage = 'TRUE'
--itlearner???????????

UPDATE  #temp
SET     reserved = REPLACE(reserved?? 'KB'?? '') ??
        datainfo = REPLACE(datainfo?? 'KB'?? '') ??
        index_size = REPLACE(index_size?? 'KB'?? '') ??
        unused = REPLACE(unused?? 'KB'?? '')

INSERT  INTO dbo.tablespaceinfo
        SELECT  nameinfo ??
                CAST(rowsinfo AS INT) ??
                CAST(reserved AS INT) ??
                CAST(datainfo AS INT) ??
                CAST(index_size AS INT) ??
                CAST(unused AS INT)
        FROM    #temp


DROP TABLE #temp
SELECT  Table_Name ??
        Rows_Count ??
        CASE WHEN reserved > 1024
             THEN CAST(reserved / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(reserved AS VARCHAR(10)) + 'KB'
        END AS Data_And_Index_Reserved ??
        CASE WHEN datainfo > 1024
             THEN CAST(datainfo / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(datainfo AS VARCHAR(10)) + 'KB'
        END AS Used ??
        CASE WHEN Index_size > 1024
             THEN CAST(index_size / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(index_size AS VARCHAR(10)) + 'KB'
        END AS index_size ??
        CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(unused AS VARCHAR(10)) + 'KB'
        END AS unused
FROM    dbo.tablespaceinfo
ORDER BY reserved DESC

???????н???????