database, log, table, index size

-- database size, table size 
SP_SPACEUSED 
GO

-- log space 
dbcc sqlperf(logspace)
go
dbcc loginfo 
go
select * from sys.dm_db_log_info(6)
go

-- database size 
SELECT 
  A.fileid, 
  A.groupid, 
  A.size, 
  A.maxsize, 
  A.growth, 
  A.status, 
  A.perf, 
  B.NAME DBNAME, 
  A.NAME DBNAMEDETAIL, 
  A.filename, 
  COMPUTEDSIZEMB = Cast(A.size / 128.AS INT), 
  COMPUTEDGROWTH = CASE WHEN A.status & 0X100000 = 0X100000 THEN A.growth ELSE Cast(A.growth / 128.AS INT) END, 
  COMPUTEDGROWTHUNIT = CASE WHEN A.status & 0X100000 = 0X100000 THEN 'PERCENT' ELSE 'MB' END 
FROM 
  master.dbo.sysaltfiles A WITH (nolock) 
  JOIN master.dbo.sysdatabases B WITH (nolock) ON A.dbid = B.dbid 
WHERE 
  B.NAME NOT IN (
    'MASTER', 'MSDB', 'TEMPDB', 'MODEL', 
    'LAZYLOG'
  )

-- database size
SELECT 
  Db_name() AS DBNAME, 
  NAME AS FILENAME, 
  type_desc, 
  size / 128.0 AS CURRENTSIZEMB, 
  size / 128.0 - Cast(
    Fileproperty(NAME, 'SPACEUSED') AS INT
  ) / 128.0 AS FREESPACEMB 
FROM 
  sys.database_files 
WHERE 
  type IN (0, 1);

-- all database size
if object_id('tempdb..#filesize') is not null 
drop table #filesize
create table #filesize
(
dbname nvarchar(128), 
filename nvarchar(128), 
type_desc nvarchar(128), 
currentsizemb decimal(10, 2), 
freespacemb decimal(10, 2)
);
go 
insert into #filesize(dbname, filename, type_desc, currentsizemb, freespacemb)
exec sp_msforeachdb 'use [?]; 
 select db_name() as dbname, 
        name as filename, 
        type_desc,
        size/128.0 as currentsizemb,  
        size/128.0 - cast(fileproperty(name, ''spaceused'') as int)/128.0 as freespacemb
from sys.database_files
where type in (0,1);';
go 
select * from #filesize where dbname not in ('distribution', 'master', 'model', 'msdb') 
go 
drop table #filesize;
go

-- fragmentation check takes a long time
SELECT 
  OBJECT_SCHEMA_NAME(IPS.OBJECT_ID) AS SCHEMA_NAME, 
  OBJECT_NAME(IPS.OBJECT_ID) AS OBJECT_NAME, 
  I.NAME AS INDEX_NAME, 
  I.TYPE_DESC AS INDEX_TYPE, 
  IPS.AVG_FRAGMENTATION_IN_PERCENT, 
  IPS.AVG_PAGE_SPACE_USED_IN_PERCENT, 
  IPS.PAGE_COUNT, 
  IPS.ALLOC_UNIT_TYPE_DESC 
FROM 
  SYS.DM_DB_INDEX_PHYSICAL_STATS(
    DB_ID(), 
    DEFAULT, 
    DEFAULT, 
    DEFAULT, 
    'SAMPLED'
  ) AS IPS 
  INNER JOIN SYS.INDEXES AS I ON IPS.OBJECT_ID = I.OBJECT_ID 
  AND IPS.INDEX_ID = I.INDEX_ID 
ORDER BY 
  PAGE_COUNT DESC;

GO

-- tempdb size
SELECT 
  CAST(
    SUM(
      (TOTAL_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS TOTAL_SIZE_MB, 
  --WORKS ON SQL 2012 AND FUTURE EDITIONS
  CAST(
    SUM(
      (UNALLOCATED_EXTENT_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS FREE_SPACE_MB, 
  CAST(
    SUM(
      (
        USER_OBJECT_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS USER_OBJECTS_MB, 
  CAST(
    SUM(
      (
        INTERNAL_OBJECT_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS INTERNAL_OBJECTS_MB, 
  CAST(
    SUM(
      (
        VERSION_STORE_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS VERSION_STORE_MB, 
  CAST(
    SUM(
      (MIXED_EXTENT_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS MIXED_EXTENT_MB 
FROM 
  TEMPDB.SYS.DM_DB_FILE_SPACE_USAGE
GO

-- tempdb capacity used by sessions
SELECT 
  GETDATE() AS RUNTIME, 
  A.*, 
  B.KPID, 
  B.BLOCKED, 
  B.LASTWAITTYPE, 
  B.WAITRESOURCE, 
  DB_NAME(B.DBID) AS DATABASE_NAME, 
  B.CPU, 
  B.PHYSICAL_IO, 
  B.MEMUSAGE, 
  B.LOGIN_TIME, 
  B.LAST_BATCH, 
  B.OPEN_TRAN, 
  B.STATUS, 
  B.HOSTNAME, 
  B.PROGRAM_NAME, 
  B.CMD, 
  B.LOGINAME, 
  REQUEST_ID 
FROM 
  SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A 
  INNER JOIN SYS.SYSPROCESSES B ON A.SESSION_ID = B.SPID

-- table size
SELECT 
  T.NAME AS TABLENAME, 
  S.NAME AS SCHEMANAME, 
  P.ROWS, 
  SUM(A.TOTAL_PAGES) * 8 AS TOTALSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.TOTAL_PAGES) * 8
        ) / 1024.00
      ), 
      2
    ) AS NUMERIC(36, 2)
  ) AS TOTALSPACEMB, 
  SUM(A.USED_PAGES) * 8 AS USEDSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.USED_PAGES) * 8
        ) / 1024.00
      ), 
      2
    ) AS NUMERIC(36, 2)
  ) AS USEDSPACEMB, 
  (
    SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
  ) * 8 AS UNUSEDSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
        ) * 8
      ) / 1024.00, 
      2
    ) AS NUMERIC(36, 2)
  ) AS UNUSEDSPACEMB 
FROM 
  SYS.TABLES T 
  INNER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID 
  INNER JOIN SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID 
  AND I.INDEX_ID = P.INDEX_ID 
  INNER JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID 
  LEFT OUTER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID 
WHERE 
  T.NAME NOT LIKE 'DT%' 
  AND T.IS_MS_SHIPPED = 0 
  AND I.OBJECT_ID > 255 
GROUP BY 
  T.NAME, 
  S.NAME, 
  P.ROWS 
ORDER BY 
  TOTALSPACEMB DESC, 
  T.NAME

-- index stats takes a long time (commentout 	,STAT3.*)
IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	,PS.NAME AS PTNFUNCNAME
	,D.PARTITIONCNT AS PTNCNT
	,T1.TYPE_DESC
	,FI.NAME AS FGNAME
	,SCHEMA_NAME(SCHEMA_ID) AS SNAME
	,T1.CREATE_DATE AS TBLCREDATE
	,T1.NAME AS OBJNAME
	,I.TYPE_DESC ISCLUSTERORHEAP
	,I.NAME AS IXNAME
	,INDEXSIZEKB AS IXSIZEKB
	,SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	,SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	,STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	,I.IS_UNIQUE
	,I.IS_UNIQUE_CONSTRAINT
	,I.IS_PRIMARY_KEY
	,I.HAS_FILTER
	,I.FILTER_DEFINITION
	,I.IS_DISABLED
	,STAT1.*
	,STAT2.*
	,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL)
	WHERE 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID                    AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.dm_db_index_physical_stats(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL, NULL)
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	,2
	,3
	,4
	,5
	,6
	,7
	,I.INDEX_ID
GO

EXEC sp_helpindex2
GO