.::: Query Check ServerName, Database Name, Database ID, Table ID, Object ID,Object Name, Size Table, master_files, size Database :::.

1. Info ServerName, Version, db_name
select @@SERVERNAME as ServerName,
       @@SERVICENAME as ServiceName,
       db_name() as DBName,
       @@VERSION as VersionDB,
       @@MICROSOFTVERSION as MICROSOFTVERSION,
       @@LANGUAGE as Language
 

   
2 . Table ID / object ID
A. Check object_id/ID table from table
SELECT @@servername as ServerName, db_name() as [DBName],
  object_id('barang') as ID_table_barang,
  object_id('customer') as ID_table_customer,
  object_id('suplier') as ID_table_suplier,
  object_id('pasok') as ID_table_pasok,
  object_id('pembelian') as ID_table_pembelian
 
B. Compare object_id/ID table with table name
SELECT @@servername as ServerName, db_name() as [DBName],
  name,
  object_id,
  OBJECT_ID(name) AS [OBJECT_ID(name)]
FROM sys.objects

-- or

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'

order by object_id

 -- or
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

3. Database ID
state_desc, recovery_model_desc, physical_database_name
select @@servername as ServerName, name as dbname, database_id, state_desc, recovery_model_desc, physical_database_name from sys.databases;

select @@servername as ServerName, db_name() as DBName_current, name as dbname, database_id, state_desc, recovery_model_desc,
physical_database_name from sys.databases
where database_id > 4 ; 

 

select @@servername as ServerName, name as dbname, database_id, state_desc, recovery_model_desc, physical_database_name from sys.databases where name like '%teguhth%';

4. size Table
SELECT @@servername as ServerName, db_name() as DBName,
    t.NAME AS TableName,    
    p.rows AS RowCounts,
    CONVERT(DECIMAL,SUM(a.total_pages)) * 8   AS TotalSpaceKB,
    SUM(a.used_pages)  * 8  AS UsedSpaceKB ,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8   AS UnusedSpaceKB
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 = 'barang' or t.NAME = 'customer' or  t.NAME = 'suplier'  or t.NAME = 'pasok' or t.NAME = 'pembelian'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    UsedSpaceKB DESC, t.Name

5. Check Master file database SQL
SELECT @@servername as ServerName, db_name() as DBName,database_id,file_id,
    type_desc,name,physical_name, state_desc,growth
    from sys.master_files where name ='teguhth' or name ='teguhth_log'

    
6. Check database size
SELECT @@servername as ServerName, db_name() as DBName_current, 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'

 -- or
 
SELECT @@servername as ServerName, db_name() as DBName_current,
      database_name = 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