fn_Util_IsTablePartitionedAtValue: checking if a specific value is at the boundary point of table partition

---------------------------------------------------------------------------------------------------------------
-- Author    Date   
-- Steven Rao   2013-06-18  
---------------------------------------------------------------------------------------------------------------
-- Assumption:
-- 1) if table is partitioned, it will be using LEFT range type
-- 2) partition function used has parameter of integer data type
-- 3) return value:
-- 1: table @tableName is partitioned and @partitionValue is one of the partition boundry point
--  0: table @tableName is partitioned but @partitionValue is not a partition boundary point
--  -1: table @tableName is not partioned at all
---------------------------------------------------------------------------------------------------------------
-- Usage example
-- SELECT [fn_Util_IsTablePartitionedAtValue] ('MyTable', 1000000, 'dbo')
---------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [fn_Util_IsTablePartitionedAtValue]
(
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
)
RETURNS INT
AS
BEGIN
DECLARE @boudaryID int
SELECT
 TOP 1
 -- boundary_id start with 1
 @boudaryID=isnull(rv.boundary_id, 0)
 FROM sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   -- check table storage type
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   INNER JOIN sys.partitions p
   ON p.object_id = i.object_id
   AND p.index_id = i.index_id
   INNER JOIN  sys.index_columns  ic
   ON (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   INNER JOIN sys.partition_schemes ps
   ON ps.data_space_id = i.data_space_id
   INNER JOIN sys.partition_functions pf
   ON pf.function_id = ps.function_id
   LEFT JOIN sys.partition_range_values rv
   ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
   -- need exact match, assuming left range type
   AND cast(rv.value as int)=@partitionValue
 WHERE -- only look for heap or clustered index
   i.index_id IN (0, 1) 
   AND s.name=@schemaName
   AND t.name=@tableName
 ORDER BY isnull(rv.boundary_id, 0) DESC
 -- if @boudaryID is null then table is NOT partitioned
 -- if @boudaryID>0 then we fall into a partition boundary point
 -- if @boudaryID=0 table is partitioned but value is not a boundary point
 RETURN ISNULL(case when @boudaryID>0 then 1 else @boudaryID end, -1)
END