Skip to content

Instantly share code, notes, and snippets.

@kristinaconley

kristinaconley/PartitionData

Created July 19, 2013 20:25
Show Gist options
  • Save kristinaconley/6042091 to your computer and use it in GitHub Desktop.
Save kristinaconley/6042091 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
PartitionData
DECLARE
@table_name sysname = NULL -- Specify table name
, @partitioned_tables bit = NULL -- NULL: all tables; 0: un-partitioned tables; 1: partitioned tables
, @aligned_indexes bit = NULL -- NULL: all indexes; 0: un-aligned indexes; 1: aligned indexes
, @right_aligned bit = NULL -- NULL: all partition functions; 0: left-aligned partition functions; 1: right-aligned partition functions
, @index_types smallint = NULL -- NULL: all indexes; 0: heaps; 1: clustered indexes; 2: heaps and clustered indexes; 3: non-clustered indexes
, @debug bit = 0 -- 0: dynamic SQL statement is not printed; 1 dynamic SQL statement is printed
SET NOCOUNT ON
SET XACT_ABORT ON
-------------------------------------------------------------------------------------------
-- DECLARE VARIABLES
-------------------------------------------------------------------------------------------
DECLARE
@sql NVARCHAR(MAX)
, @params NVARCHAR(4000)
-------------------------------------------------------------------------------------------
-- CREATE DYNAMIC SQL STATEMENT
-------------------------------------------------------------------------------------------
SET @sql = '
WITH partitioned_tables
AS
(
SELECT
partitioned_tables.object_id
, partitioned_tables.name AS [table_name]
, CASE partitioned_tables.type
WHEN ''PS'' THEN 1
ELSE 0
END AS [table_is_partitioned]
, si.index_id
, CASE si.index_id
WHEN 0 THEN ''HEAP''
ELSE si.name
END AS [index_name]
, CASE sds.type
WHEN ''PS'' THEN 1
ELSE 0
END AS [index_is_aligned]
, sds.data_space_id
FROM
sys.indexes AS si
INNER JOIN
(
SELECT
st.object_id
, st.name
, sds.type
FROM
sys.tables AS [st]
LEFT OUTER JOIN
sys.indexes AS [si]
ON
st.object_id = si.object_id
LEFT OUTER JOIN
sys.data_spaces AS [sds]
ON
si.data_space_id = sds.data_space_id
WHERE
si.index_id IN (0,1)
) AS partitioned_tables
ON
si.object_id = partitioned_tables.object_id
LEFT OUTER JOIN
sys.data_spaces AS [sds]
ON
si.data_space_id = sds.data_space_id
)
SELECT
pt.table_name AS [table_name]
, pt.table_is_partitioned AS [table_is_partitioned]
, pt.index_id
, pt.index_name
, pt.index_is_aligned
, sps.name AS [partition_scheme]
, spf.name AS [partition_function]
, sc.name AS [partition_key]
, spf.boundary_value_on_right AS [right_aligned]
, sprv_left.value AS [lower_boundary]
, sprv_right.value AS [upper_boundary]
, sddps.partition_number
, sfg.data_space_id AS [file_group_id]
, sfg.name AS [file_group_name]
, sddps.row_count AS [part_row_count]
/* Begin Data Usage Statics */
, CAST(sddps.used_page_count * 8 / 1024 AS NUMERIC(18,2)) AS [used_pages_mb]
, CAST(sddps.in_row_data_page_count * 8 / 1024 AS NUMERIC(18,2)) AS [data_pages_mb]
, CAST(sddps.reserved_page_count * 8 / 1024 AS NUMERIC(18,2)) AS [reserved_pages_mb]
/* End Data Usage Statics */
FROM
partitioned_tables AS [pt]
LEFT OUTER JOIN
sys.index_columns AS [sic]
ON
pt.object_id = sic.object_id
AND pt.index_id = sic.index_id
AND sic.partition_ordinal > 0
LEFT OUTER JOIN
sys.columns AS [sc]
ON
sic.object_id = sc.object_id
AND sic.column_id = sc.column_id
LEFT OUTER JOIN
sys.dm_db_partition_stats AS [sddps]
ON
pt.object_id = sddps.object_id
AND pt.index_id = sddps.index_id
LEFT OUTER JOIN
sys.partition_schemes AS [sps]
ON
pt.data_space_id = sps.data_space_id
LEFT OUTER JOIN
sys.partition_functions AS [spf]
ON
sps.function_id = spf.function_id
LEFT OUTER JOIN
sys.partition_range_values AS [sprv_right]
ON
spf.function_id = sprv_right.function_id
AND sddps.partition_number = sprv_right.boundary_id
LEFT OUTER JOIN
sys.partition_range_values AS [sprv_left]
ON
spf.function_id = sprv_left.function_id
AND sddps.partition_number - 1 = sprv_left.boundary_id
LEFT OUTER JOIN
sys.destination_data_spaces AS [sdds]
ON
pt.data_space_id = sdds.partition_scheme_id
AND sddps.partition_number = sdds.destination_id
LEFT OUTER JOIN
sys.filegroups AS [sfg]
ON
sdds.data_space_id = sfg.data_space_id
WHERE
1 = 1
AND OBJECTPROPERTYEX(pt.object_id, ''BaseType'') = ''U'''
-------------------------------------------------------------------------------------------
-- ADD PREDICATE STATEMENTS AS NECESSARY BASED ON SPECIFIED PARAMETERS
-------------------------------------------------------------------------------------------
IF @table_name IS NOT NULL
SET @sql = @sql + '
AND pt.table_name = @xtable_name'
IF @partitioned_tables IS NOT NULL
SET @sql = @sql + '
AND pt.table_is_partitioned = @xpartitioned_tables'
IF @aligned_indexes IS NOT NULL
SET @sql = @sql + '
AND pt.index_is_aligned = @xaligned_indexes'
IF @right_aligned IS NOT NULL
SET @sql = @sql + '
AND spf.boundary_value_on_right = @xright_aligned'
IF @index_types = 0
SET @sql = @sql + '
AND pt.index_id = 0'
ELSE IF @index_types = 1
SET @sql = @sql + '
AND pt.index_id = 1'
ELSE IF @index_types = 2
SET @sql = @sql + '
AND pt.index_id IN (0, 1)'
ELSE IF @index_types = 3
SET @sql = @sql + '
AND pt.index_id > 1'
-------------------------------------------------------------------------------------------
-- ADD ORDER BY TO DYNAMIC SQL STATEMENT
-------------------------------------------------------------------------------------------
SET @sql = @sql + '
ORDER BY
pt.table_name
, pt.index_id
, sddps.partition_number'
-------------------------------------------------------------------------------------------
-- PRINT OUT COMPLETED DYNAMIC SQL STATEMENT
-------------------------------------------------------------------------------------------
IF @debug = 1
PRINT @sql
-------------------------------------------------------------------------------------------
-- DEFINE sp_executesql PARAMETERS LIST AND EXECUTE DYNAMIC SQL STATEMENT
-------------------------------------------------------------------------------------------
BEGIN TRY
SET @params = '@xtable_name SYSNAME
, @xpartitioned_tables BIT
, @xaligned_indexes BIT
, @xindex_type SMALLINT
, @xright_aligned BIT'
EXECUTE sp_executesql @sql, @params, @table_name, @partitioned_tables, @aligned_indexes, @index_types, @right_aligned
END TRY
-------------------------------------------------------------------------------------------
-- ERROR HANDLER
-------------------------------------------------------------------------------------------
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE
@errmsg nvarchar(2048)
, @severity tinyint
, @state tinyint
, @errno int
, @proc sysname
, @lineno int
SELECT
@errmsg = error_message()
, @severity = error_severity()
, @state = error_state()
, @errno = error_number()
, @proc = error_procedure()
, @lineno = error_line()
IF @errmsg NOT LIKE '***%' -- 11
BEGIN
SELECT
@errmsg = '*** ' + coalesce(quotename(@proc)
, '<dynamic SQL>') + ', ' + ltrim(str(@lineno)) + '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg
RAISERROR(@errmsg, @severity, @state)
END
ELSE
RAISERROR(@errmsg, @severity, @state)
END CATCH
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment