.::: Query index fragmentation level SQL Server with detail, specify Table, Database :::.

1. index fragmentation level

SELECT @@servername as Server, DB_NAME(ips.database_id) AS DatabaseName,
       SCHEMA_NAME(ob.[schema_id]) SchemaNames,
       ob.[name] AS ObjectName,
       ix.[name] AS IndexName,
       ob.type_desc AS ObjectType,
       ix.type_desc AS IndexType,
       -- ips.partition_number AS PartitionNumber,
       ips.page_count AS [PageCount], -- Only Available in DETAILED Mode
       ips.record_count AS [RecordCount],
       ips.avg_fragmentation_in_percent AS AvgFragmentationInPercent
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') ips
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') ips -- QuickResult
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id]
                AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND ix.[type] IN(1,2,3,4)
AND ix.is_disabled = 0
AND ix.is_hypothetical = 0
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ips.index_level = 0
-- AND ips.page_count >= 1000 -- Filter to check only table with over 1000 pages
-- AND ips.record_count >= 100 -- Filter to check only table with over 1000 rows
-- AND ips.database_id = DB_ID() -- Filter to check only current database
-- AND ips.avg_fragmentation_in_percent > 50 -- Filter to check over 50% indexes
-- ORDER BY DatabaseName
order by ips.avg_fragmentation_in_percent desc, DatabaseName asc

.::: 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
 

.::: Create Store Procedure & Check Dependencies :::.

A. Sample Query 1 & Store Procedure 1
1. Sample 1
-- Query 1
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

2. Store Procedure 1

Create Procedure Barang_pasok

as
 begin
 select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from   barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and  s.KODE_SUPLIER=p.KODE_SUPLIER;

end

.::: Insert Into table from Linkedserver to Linkedserver with Open Query & dbname(database_id) :::.

A. Linked Server (MSSQL)

1. Check Database ID on Linked Server
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases

2. Check Result Query on Linked Server
SELECT @@Servername as LocalServer, @@Servername as ServerName_Link,db_name('5')  as [DBName], * from teguhth.dbo.barang