MSSQL Server Instance ve Veritabanı Altındaki Partition  Tabloları Bulmak

Bu makalede veritabanı altında bulunan tablolardan hangisinin Partition yapısında olduğunu bulmuş olacağız. Sayfamızın partition makalelerinde bu komutu bulmak mümkün. Kullanıcıların Tabloların create script’ini almak istediklerinde partition tablolarda bu yöntem geçerli olmayacaktır. Bunun için veritabanı altında bulunan tabloların partition’lı olanını bulup Generate Script’inin alınması gerekmektedir.

İnstance altında bulunan Tüm veritabanlarını tarar ve partition olan veritabanı altındaki tabloları getirmektedir..

DECLARE @command varchar(1000) 

SELECT @command = 'USE [?] SELECT

DB_NAME(),OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],

t.name AS [Table Name],

i.name AS [Index Name],

s.name AS [Partition Scheme], *

FROM sys.indexes i

INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

INNER JOIN sys.tables t ON i.object_id = t.object_id '

EXEC sp_MSforeachdb @command

Tüm db’leri teker teker TARAR ve partition yapılmış veritabanlarının tablolarını gösterir.

İnstance bazlı başka bir komut.

DECLARE @command nvarchar(max)
SELECT @command = 'USE [?]
SELECT
databaseName = DB_NAME(),
OBJECT_NAME(si.object_id) AS object_name
,pf.NAME AS pf_name
,ps.NAME AS partition_scheme_name
,p.partition_number
,ds.NAME AS partition_filegroup
,rv.value AS range_value
,(
CASE pf.boundary_value_on_right
WHEN 0
THEN ''RAGE_LEFT''
ELSE ''RANGE_RIGHT''
END
) AS range_direction
,SUM(CASE
WHEN si.index_id IN (
1
,0
)
THEN p.rows
ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0
THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
GROUP BY ds.NAME
,p.partition_number
,pf.NAME
,pf.type_desc
,pf.fanout
,pf.boundary_value_on_right
,ps.NAME
,si.object_id
,rv.value
ORDER BY p.partition_number
'
--print @command
EXEC sp_MSforeachdb @command

2. Partition’ı tabloları database bazlı bulmak  istersek aşağıdaki komut kullanılmaktadır.

USE a 
SELECT
DB_NAME(),OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
t.name AS [Table Name],
i.name AS [Index Name],
s.name AS [Partition Scheme], *
FROM sys.indexes i
INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
INNER JOIN sys.tables t ON i.object_id = t.object_id

3. Herhangi bir veritabanı altındaki partition tabloları görmek için aşağıdaki script kullanılır.

select distinct tbl.name
from sys.partitions ptt
inner join sys.tables tbl
on ptt.object_id = tbl.object_id
where ptt.partition_number <> 1

4. Aşağıdaki komut ile veritabanı altında bulunan partition tablo ve partition sız tabloları getirmektedir. Aynı zamanda tüm tabloların filegroupları görülmektedir.

SELECT 
    SCHEMA_NAME(t.schema_id) AS [Schema],
    t.name AS [Table Name],
    CASE 
        WHEN ps.data_space_id IS NOT NULL THEN 'YES'
        ELSE 'NO'
    END AS [Is Partitioned],
    ISNULL(ps.name, 'N/A') AS [Partition Scheme],
    ISNULL(pf.name, 'N/A') AS [Partition Function],
    -- Filegroup bilgisi (partition olmayanlar için)
    CASE 
        WHEN ps.data_space_id IS NULL THEN fg.name 
        ELSE 'See Partition Details Below'
    END AS [Filegroup (Non-Partitioned)],
    -- Partition detayları (partition olanlar için)
    p.partition_number AS [Partition Number],
    pfg.name AS [Partition Filegroup],
    p.rows AS [Rows in Partition],
    prv.value AS [Partition Boundary Value]
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1) -- Heap or Clustered Index
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes ps ON ds.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.filegroups fg ON ds.data_space_id = fg.data_space_id AND ps.data_space_id IS NULL
LEFT JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id AND p.partition_number = dds.destination_id
LEFT JOIN sys.filegroups pfg ON dds.data_space_id = pfg.data_space_id
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id 
    AND p.partition_number = CASE pf.boundary_value_on_right 
        WHEN 1 THEN prv.boundary_id + 1 
        ELSE prv.boundary_id 
    END
WHERE t.is_ms_shipped = 0 -- Only user-created tables
ORDER BY [Schema], [Table Name], [Partition Number];

5. Sadece partition tabloların filegroup yapısını görmek için aşağıdaki komut kullanılmaktadır.

SELECT 
    SCHEMA_NAME(t.schema_id) AS [Schema],
    t.name AS [Table Name],
    ps.name AS [Partition Scheme],
    pf.name AS [Partition Function],
    dds.destination_id AS [Partition Number],
    fg.name AS [Filegroup Name],
    p.rows AS [Rows in Partition],
    prv.value AS [Partition Boundary Value]
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1) -- Heap or Clustered Index
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id 
    AND i.index_id = p.index_id 
    AND dds.destination_id = p.partition_number
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id 
    AND p.partition_number = CASE pf.boundary_value_on_right 
        WHEN 1 THEN prv.boundary_id + 1 
        ELSE prv.boundary_id 
    END
WHERE t.is_ms_shipped = 0 -- Only user-created tables
ORDER BY [Schema], [Table Name], [Partition Number];

Bu makalede veritabanı altındaki partition yapılmış tabloların komutlarını görmüş olduk. Başka bir makalede görüşmek dileğiyle.

Onlar, “Insanlarn Kusurlarini Affederler. ” Ali Imran-134