SQL SERVER INDEX SIZE AND USAGE

 

SELECT object_name(i.object_id) as table_name

,COALESCE(i.name, space(0)) as index_name

,ps.partition_number

,ps.row_count

,Cast((ps.reserved_page_count * 8)/1024. as decimal(12,2)) as size_in_mb

,COALESCE(ius.user_seeks,0) as user_seeks

,COALESCE(ius.user_scans,0) as user_scans

,COALESCE(ius.user_lookups,0) as user_lookups

,i.type_desc

FROM sys.all_objects t

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = db_id() AND i.object_id = ius.object_id AND i.index_id = ius.index_id

ORDER BY object_name(i.object_id), i.name

 

table and index storage size

with pages as (

    SELECT object_id, SUM (reserved_page_count) as reserved_pages, SUM (used_page_count) as used_pages,

            SUM (case

                    when (index_id < 2) then (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

                    else lob_used_page_count + row_overflow_used_page_count

                 end) as pages

    FROM sys.dm_db_partition_stats

    group by object_id

), extra as (

    SELECT p.object_id, sum(reserved_page_count) as reserved_pages, sum(used_page_count) as used_pages

    FROM sys.dm_db_partition_stats p, sys.internal_tables it

    WHERE it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id

    group by p.object_id

)

SELECT object_schema_name(p.object_id) + '.' + object_name(p.object_id) as TableName, (p.reserved_pages + isnull(e.reserved_pages, 0)) * 8 as reserved_kb,

        pages * 8 as data_kb,

        (CASE WHEN p.used_pages + isnull(e.used_pages, 0) > pages THEN (p.used_pages + isnull(e.used_pages, 0) - pages) ELSE 0 END) * 8 as index_kb,

        (CASE WHEN p.reserved_pages + isnull(e.reserved_pages, 0) > p.used_pages + isnull(e.used_pages, 0) THEN (p.reserved_pages + isnull(e.reserved_pages, 0) - p.used_pages + isnull(e.used_pages, 0)) else 0 end) * 8 as unused_kb

from pages p

left outer join extra e on p.object_id = e.object_id

ORDER BY index_kb DESC

Converting MySQL data to XML

$xmlDom = new DOMDocument();
$xmlDom->appendChild($xmlDom->createElement('results'));
$xmlRoot = $xmlDom->documentElement;

while ( $row = mysql_fetch_row($result) ) 
    {
      $xmlRowElementNode = $xmlDom->createElement('row');

      $i=0;
      for($i=0;$i<mysql_num_fields($result);$i++)
      {
          $xmlRowElement = $xmlDom->createElement(mysql_field_name($result,$i));
          $xmlText = $xmlDom->createTextNode($row[$i]);
            $xmlRowElement->appendChild($xmlText);

            $xmlRowElementNode->appendChild($xmlRowElement);
      }

      $xmlRoot->appendChild($xmlRowElementNode);
   }


    header('Content-type:  text/xml');
    echo $xmlDom->saveXML();

<results>
    <row1>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row1>
    <row2>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row2>
    <row3...>
         <fieldname1>value</fieldname1>
         <fieldname2>value</fieldname2>
         <fieldname3>value</fieldname3>
         <fieldname4...>value</fieldname4...>
    </row3...>
</results>

sql server all temp table sizes

use tempdb

--1

select object_name(p.object_id),

    sum(au.total_pages)*8 as [space_in_kb],

    sum(au.total_pages)*8/1024 as [space_in_mb],

    sum(au.total_pages)*8/1024/1024 as [space_in_gb]

    from sys.partitions p

    join sys.allocation_units au on p.hobt_id = au.container_id

    group by p.object_id

    order by [space_in_kb]  desc

---2

SELECT sysobjects.[name] AS [TableName],

    SUM(sysindexes.reserved) * 8 AS [Size(KB)],

    SUM(sysindexes.dpages) * 8 AS [Data(KB)],

    (SUM(sysindexes.used) - SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],

    (SUM(sysindexes.reserved) - SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]

FROM dbo.sysindexes AS sysindexes

    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id

WHERE sysobjects.[type] = 'U'

GROUP BY sysobjects.[name]

ORDER BY [Size(KB)] DESC

recursive unique identifier with CTE

;with CTE as(

      SELECt  ID=1,

                  GID=NEWID()

            UNION ALL

      SELECT  ID+1,

                  NEWID() FROM CTE WHERE ID<100

)

 

SELECT     

      *

FROM CTE OPTION (MAXRECURSION 100)

that’s all

recursive start and end of month

 

DECLARE     @StartDate DATE = '20231215';

 

WITH cteSource(MonthSerial)

AS (

      SELECT      DATEDIFF(MONTH, '19990101', @StartDate) AS MonthSerial

 

      UNION ALL

 

      SELECT      MonthSerial - 1 AS MonthSerial

      FROM  cteSource

      WHERE MonthSerial > 0

)

SELECT      MONTH(DATEADD(MONTH, MonthSerial, '19990101')) as IMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990101')as DATE) AS STARTOFMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990131')as DATE) AS ENDOFMONTH

FROM  cteSource

OPTION      (MAXRECURSION 0)

Switch/Case with Substring

SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)