I have access to a database and I need to know the Partition Scheme definitions in the database. i.e. I need to know the partition scheme name, which Partition function is it using, what file groups are the partitions assigned, etc...
For example someone creates a partition scheme as so (taken from msdn):
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
Then I want the name: myRangePS1, the function: myRangePF1, and the partitions: (test1fg, test2fg, test3fg, test4fg), Whether it is partition ALL or not
How would I go about this using SQL statements only? I can query the names and some data about partitions by using the system view sys.partition_scheme, but it is not enough.
The below shows a similar solution on finding the definition of Partition functions: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d0ce92e3-bf48-455d-bd89-c334654d7e97/how-to-find-partition-function-text-applied-to-a-table
3条答案
按热度按时间hjqgdpho1#
I have modified knkarthick24's first query to show Partition function values associated to each file group:
This is the query I was looking for and I hope other people can make use of this!
lbsnaicq2#
Please try this query: 1)
or for more detailed information use the below query( SQL 2008 Internals Book)
xxb16uws3#
I modified the knkarthick24 script to make it a little more user-friendly in understanding the function definition. In addition, my script returns data for all tables that have been partitioned the user: