Teguh Triharto Learning Center: .::: Object ID, ObJect Name, Physical Index, database size, index size, drive size in MSSQL SQL Server :::.

274100017 = Person
2101582525 = EmployeeDepartmentHistory

1. List Object ID, Object Name, Table Name
SELECT @@servername as ServerName,
       db_name() as [DBName],
       name as TableName,
       object_id,
       OBJECT_ID(name) AS [OBJECT_ID(name)]
FROM sys.objects
-- where name = 'barang' or name = 'customer' or name = 'suplier' or
-- name = 'pasok' or name = 'pembelian' or name = 'sysdiagrams'
where name in ('barang','customer','suplier','pasok','pembelian','sysdiagrams')
order by object_id

2. List specify Object ID, Object Name, Table Name example table barang
select @@servername as ServerName, db_name() as [DBName],* from teguhth.sys.objects where name ='barang'

-- or

select @@servername as ServerName, db_name() as [DBName],* from sys.objects where name ='Person' or name ='EmployeeDepartmentHistory'

-- or

select @@servername as ServerName, db_name() as [DBName],* from sys.objects where name in ('Person','EmployeeDepartmentHistory')

3. List specify physical index a table/ object name
select @@servername as ServerName, db_name() as [DBName],OBJECT_SCHEMA_NAME(object_id) as SchemaName,object_name(object_id) as ObjectName,* from sys.indexes
where object_id = (select object_id from sys.objects where name = 'barang')
order by object_name(object_id),name

-- or

select @@servername as ServerName, db_name() as [DBName],OBJECT_SCHEMA_NAME(object_id) as SchemaName,object_name(object_id) as ObjectName,* from sys.indexes
where object_id = (select object_id from sys.objects where name = 'Person')
order by object_name(object_id),name

-- or

select @@servername as ServerName, db_name() as [DBName],OBJECT_SCHEMA_NAME(object_id) as SchemaName,object_name(object_id) as ObjectName,* from sys.indexes
where object_id = (select object_id from sys.objects where name = 'Person') or object_id = (select object_id from sys.objects where name = 'EmployeeDepartmentHistory')
order by object_name(object_id),name

--or

select @@servername as ServerName, db_name() as [DBName],OBJECT_SCHEMA_NAME(object_id) as SchemaName,object_name(object_id) as ObjectName,* from sys.indexes
where object_id in (274100017,2101582525)
order by object_name(object_id),name

4. List all index a table/ object name
select @@servername as ServerName, db_name() as [DBName],object_id, name from sys.indexes
where object_id in (select object_id from sys.objects )

5. size database file
SELECT @@servername as ServerName, 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,
    size/128.0 - (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) as UsageSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

SELECT DB_NAME(database_id) AS DatabaseName,
       Name AS Logical_Name,
       Physical_Name,
       (size * 8) / 1024 SizeMB
FROM sys.master_files
-- WHERE DB_NAME(database_id) = 'teguhth'

6. Database & Log File

SELECT
      @@servername as ServerName,DBName = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db
GROUP BY database_id;

SELECT
      @@servername as ServerName,DBName = DB_NAME(database_id)
    , CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as LogFileMb
    , CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as DataFileMb
    , CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) as TotalSizeMb
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db
GROUP BY database_id;

7. index list index physical only >> perlu di coba
SELECT @@servername as ServerName,db_name() as [DBName],
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
--   object_name(QT.OBJECTID) as ObjectName,
     ind.*,
     ic.*,
     col.*
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
WHERE
     ind.is_primary_key = 0
     AND ind.is_unique = 0
     AND ind.is_unique_constraint = 0
     AND t.is_ms_shipped = 0
   and t.name ='EmployeeDepartmentHistory' or t.name ='Person'
ORDER BY
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

7. index size
SELECT @@servername as ServerName,db_name() as [DBName],
i.[name] AS IndexName
    ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
    AND s.[index_id] = i.[index_id]
---where i.[name] ='AK_Address_rowguid'
GROUP BY i.[name]
ORDER BY i.[name]
GO

8. drive size ( open power shell )

gwmi win32_logicaldisk | Format-Table DeviceId, Volumename, @{n="Size";e={[math]::Round($_.Size/1GB,2)}},@{n="FreeSpace";e={[math]::Round($_.FreeSpace/1GB,2)}},@{Name="Used Space (GB)"; Expression={([math]::Round(($_.Size -$_.FreeSpace)/1GB,2)) }}