Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Partition Scheme DMVs

-- Partition Functions:
SELECT PF.function_id, PF.name, PF.fanout AS NumPartitions, 
CASE WHEN PF.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END AS RangeType, 
PP.parameter_id, 
CASE WHEN PP.system_type_id = PP.user_type_id THEN T1.name ELSE T2.name END AS ParameterDataType
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP ON PF.function_id = PP.function_id
JOIN sys.types AS T1 ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2 ON T2.user_type_id= PP.user_type_id


-- Index - Partitioned Table - Partition Scheme - Partition - Function
SELECT SI.name AS IndexName, OBJECT_NAME(SI.object_id) AS PartitionedTable, 
DS.name AS PartitionScheme, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.index_id IN(0,1)


-- Index - Partitioned Table - Partition Scheme - Partition - Function - more detailed
SELECT SI.name AS IndexName,  OBJECT_NAME(SI.object_id) AS PartitionedTable, DS.name AS PartitionSchemeName, PF.name AS PartitionFunction, 
P.partition_number AS PartitionNumber, P.rows AS PartitionRows, FG.name AS FileGroupName
FROM sys.partitions AS P
JOIN sys.indexes AS SI ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.destination_data_spaces AS DDS ON DDS.partition_scheme_id = SI.data_space_id AND DDS.destination_id = P.partition_number
JOIN sys.filegroups AS FG ON DDS.data_space_id = FG.data_space_id
WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.type IN(0,1)

No comments:

Post a Comment