MD3.f_GetIdxFileGroupOrPartition.sql 1.34 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.f_GetIdxFileGroupOrPartition'))
  DROP FUNCTION MD3.f_GetIdxFileGroupOrPartition
GO

--returns either the filegroup or partition scheme/column for a given index

CREATE FUNCTION MD3.f_GetIdxFileGroupOrPartition
(
  @SchemaName  SYSNAME,
  @TblName     SYSNAME,
  @IdxName     SYSNAME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE
      @DataSpace VARCHAR(MAX),
      @FullName SYSNAME

  SELECT
      @FullName = @SchemaName + '.' + @TblName

  SELECT @DataSpace = CASE sds.type 
                         WHEN 'FG' THEN sds.name
                         WHEN 'PS' THEN sds.name + '(' + sc.name + ')'
                         ELSE NULL
                      END 
    FROM sys.tables st 
    JOIN sys.indexes si
      ON st.object_id = si.object_id
    JOIN sys.index_columns sic
      ON st.object_id = sic.object_id
     AND si.index_id = sic.index_id 
    JOIN sys.data_spaces sds
      ON si.data_space_id = sds.data_space_id
    JOIN sys.columns sc
      ON sc.object_id = sic.object_id and sc.column_id = sic.column_id   
   WHERE st.object_id = OBJECT_ID(@FullName)
     AND si.name = @IdxName
     AND (    (sds.type = 'FG' AND sic.key_ordinal = 1)
           OR (sds.type = 'PS' AND sic.partition_ordinal = 1)
         )

  RETURN @DataSpace
END
GO