User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

BEGIN -- Code block
  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET DEADLOCK_PRIORITY LOW
  --
   SET NOCOUNT ON
   --
   DECLARE @DBName VARCHAR(128)
   DECLARE @SQLString VARCHAR (2000)
   DECLARE c_db CURSOR FOR
       SELECT name
       FROM master.dbo.sysdatabases
       WHERE status&512 = 0
         AND dbid > 4
   -- 
   CREATE TABLE #TempForTableStats([DatabaseName]        VARCHAR(120),
                                   [Table]               VARCHAR(200),
                                   [Rows]                INT,
                                   [Data(MB)]            DECIMAL(12,2),
                                   [Index(MB)]           DECIMAL(12,2),
                                   [Total(MB)]           DECIMAL(12,2))
                                   
   --
   OPEN c_db
   FETCH NEXT FROM c_db INTO @DBName
   WHILE @@FETCH_STATUS = 0
      BEGIN
         SET @SQLString = 'USE [' + rtrim(@DBName)  + '] '  +
'SELECT ' + '''' + @DBName + '''' + '                 AS  [Database],  '           +  
'         OBJECT_NAME(si.id)                          AS  [Table],     '           +
'         MAX(si.rows)                                AS  [Rows],      '           +
'         CAST(SUM(CASE WHEN si.indid IN (0,1)  THEN si.dpages * 8/1024.00 '  +
'                       ELSE 0                                    '           +
'                   END)   AS DECIMAL(10,2))          AS [Data(MB)],  '           +
'         CAST(SUM(CASE WHEN si.indid NOT IN (0,1)  THEN si.dpages * 8/1024.00 '       +
'                       ELSE 0                                             '           +
'                  END)   AS DECIMAL(10,2))           AS [Index(MB)], '           +
'         CAST(SUM (si.dpages * 8/1024.00)  AS DECIMAL(12,2))                   AS [Total(MB)] ' +
'FROM sysindexes si ' +
'WHERE ISNULL(INDEXPROPERTY(si.id, si.name, ''IsStatistics''),0)   = 0 ' +
'  AND ISNULL(INDEXPROPERTY(si.id, si.name, ''IsHypothetical''),0) = 0 ' +
'GROUP BY si.id '


         INSERT #TempForTableStats 
         EXECUTE(@SQLString)
          -------------------------------------------------------------------------
         FETCH NEXT FROM c_db INTO @DBName
      END  -- Cursor fetch loop, c_db
   DEALLOCATE c_db
   --
   SELECT CAST(@@SERVERNAME AS VARCHAR(60))          AS [Instance],
          CAST(RTRIM(DatabaseName) AS VARCHAR(60))   AS [Database],
          CAST(RTRIM([Table]) AS VARCHAR(60))        AS [Table],
          [Rows]               AS [Rows],
          [Data(MB)]           AS [Data(MB)],
          [Index(MB)]          AS [Index(MB)],
          [Total(MB)]          AS [Total(MB)],      
          GETDATE()            AS [DateChecked]
   FROM #TempForTableStats
   ORDER BY [DatabaseName]
   ------------
   DROP TABLE #TempForTableStats 
END  -- Code block

Add comment


Security code
Refresh

0
0
0
s2sdefault