November 30, 2017 at 9:07 am
Hi
First post, so be gentle ...
One of my clients set up their database using an sql script which included their partitioning & Filegroup info ..
I have just been notified that they have an error and asked for the sql that created the DB, unfortunately it has been deleted.
Is there a way to extract the partitioning & Filegroup info from the database ?
I can see (on my own example) that I can see the file group set up, but nothing about the partitioning.
Any help or pointers appreciated.
Steve
November 30, 2017 at 9:13 am
Steve
You're probably going to have to reverse engineer it from the catalog views. sys partitions is a good place to start. You might even find someone who's done this before if you search for something like "script out partitions".
John
November 30, 2017 at 9:42 am
Cheers John will take a look
Will set up an example DB with a simple set up (partition & FG) and see if I can get the required info from that.
Steve
November 30, 2017 at 9:46 am
To generate a script for the database, right-click on the database name in SSMS Object Explorer and select "Script database as...".
To generate scripts for tables, including the partition schemes and partition functions, some scripting options must be set. In SSMS menu open the Tools/Options dialog and go to "SQL Server Object Explorer/Scripting". Under Table and view options, set "Script partition schemes to True. Under Object scripting options, set Generate script for dependent objects.
Now table scripts generated from right clicking on tables will include the table partitioning information, the partition function and the partition scheme.
HTH,
Tim
November 30, 2017 at 10:46 am
will this help
SELECT SCHEMA_NAME(so.schema_id) AS schema_name ,
OBJECT_NAME(p.object_id) AS object_name ,
p.partition_number ,
p.data_compression_desc ,
dbps.row_count ,
dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,
si.index_id ,
CASE WHEN si.index_id = 0 THEN '(heap!)'
ELSE si.name
END AS index_name ,
si.is_unique ,
si.data_space_id ,
mappedto.name AS mapped_to_name ,
mappedto.type_desc AS mapped_to_type_desc ,
partitionds.name AS partition_filegroup ,
pf.name AS pf_name ,
pf.type_desc AS pf_type_desc ,
pf.fanout AS pf_fanout ,
pf.boundary_value_on_right ,
ps.name AS partition_scheme_name ,
rv.value AS range_value
FROM sys.partitions p
JOIN sys.objects so
ON p.object_id = so.object_id
AND so.is_ms_shipped = 0
LEFT JOIN sys.dm_db_partition_stats AS dbps
ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
JOIN sys.indexes si
ON p.object_id = si.object_id
AND p.index_id = si.index_id
LEFT JOIN sys.data_spaces mappedto
ON si.data_space_id = mappedto.data_space_id
LEFT JOIN sys.destination_data_spaces dds
ON si.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds
ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.partition_schemes AS ps
ON dds.partition_scheme_id = ps.data_space_id
LEFT 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
November 30, 2017 at 12:36 pm
goher2000 - Thursday, November 30, 2017 10:46 AMwill this help
SELECT SCHEMA_NAME(so.schema_id) AS schema_name
,OBJECT_NAME(p.object_id) AS object_name
,p.partition_number
,p.data_compression_desc
,dbps.row_count
,dbps.reserved_page_count * 8 / 1024. AS reserved_mb
,si.index_id
,CASE
WHEN si.index_id = 0
THEN '(heap!)'
ELSE si.name
END AS index_name
,si.is_unique
,si.data_space_id
,mappedto.name AS mapped_to_name
,mappedto.type_desc AS mapped_to_type_desc
,partitionds.name AS partition_filegroup
,pf.name AS pf_name
,pf.type_desc AS pf_type_desc
,pf.fanout AS pf_fanout
,pf.boundary_value_on_right
,ps.name AS partition_scheme_name
,rv.value AS range_value
FROM sys.partitions p
INNER JOIN sys.objects so ON p.object_id = so.object_id
AND so.is_ms_shipped = 0
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id = dbps.partition_id
INNER JOIN sys.indexes si ON p.object_id = si.object_id
AND p.index_id = si.index_id
LEFT JOIN sys.data_spaces mappedto ON si.data_space_id = mappedto.data_space_id
LEFT JOIN sys.destination_data_spaces dds ON si.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
LEFT 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 = rv.boundary_id + CAST(pf.boundary_value_on_right AS int);
Just gave it some formatting.
November 30, 2017 at 1:44 pm
thanks, it is readable now
December 15, 2017 at 10:37 am
Hi thanks for all your comments, making some progress now
December 15, 2017 at 7:38 pm
stevenmillward - Friday, December 15, 2017 10:37 AMHi thanks for all your comments, making some progress now
What does that actually mean?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply