I have a database with several tables that are heavily partitioned and I'm trying to optimize part of an ETL operation that involves one of those tables. The table has 1347 partitions involving 35 different file groups. I want to recreate the table in a different database but I don't see a way to get the partition scheme definition or function definition for my new database (it's not included when I script the table out in SSMS). I can get other pieces of the puzzle with the query below but I can't seem to find a way to bring it all together! I've created the new database with all the filegroups and such to match the original.
select distinct
ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('PartitionedTable')
The database is on SQL Server 2016 SP1 CU7 Enterprise Edition
