SELECT Data_located_on_filegroup = fg.groupname, Table_name = obj.nameFROM sysfilegroups fg, sysindexes idx, sysobjects objWHERE idx.id = obj.idAND obj.type in (‘S ‘,‘U ‘)AND idx.indid < 2AND idx.groupid = fg.groupid  and  obj.name like ‘%%‘       --表名ORDER BY fg.groupname

CREATE procedure [olap].[usp_SwitchOutFact_Partition](@date datetime,@tablename varchar(50))asbeginBEGIN TRANSACTIONdeclare @RangeKey1 nvarchar(100) declare @RangeKey2 nvarchar(100)   set @RangeKey1 = convert(nvarchar(100), year(@date)*10000 + month(@date)*100 + day(@date)) set @RangeKey2 =convert(nvarchar(100), year(dateadd(day, 1, @date))*10000 + month(dateadd(day, 1, @date))*100 + day(dateadd(day, 1, @date))) /* --逻辑描述: 1.先检查 临时表是不是存在, 存在则删除后 使用select into 新建表 2.select into 创建的表 默认存放在 PRIMARY 文件组, 我们要更改 文件组 3.给临时表创建索引的时候, 可以更改文件组  4.使表结构一致(包括索引) 所以删除刚创建的索引,此时完成 表的 文件组的更改 5.进行表 压缩 ,指定 WITH(DATA_COMPRESSION = PAGE 6. SWITCH PARTITION  */ if exists(select 1 from sys.tables where object_id=object_id(‘[olap].[staging_‘+@tablename+‘]‘))    exec(‘drop table [olap].[staging_‘+@tablename+‘]‘)exec (‘select top(0) * into olap.staging_‘+@tablename+‘ from olap.‘+@tablename)exec(‘CREATE CLUSTERED INDEX PK_staging_‘+@tablename+‘ ON [olap].[staging_‘+@tablename+‘]([PartitionRangeKey]) WITH(ONLINE=ON) ON [FactFG]‘)exec(‘DROP INDEX PK_staging_‘+@tablename+‘ ON [olap].[staging_‘+@tablename+‘]‘)exec(‘ALTER TABLE olap.staging_‘ + @tablename + ‘ REBUILD WITH(DATA_COMPRESSION = PAGE )‘)exec(‘ALTER TABLE [olap].[staging_‘+@tablename+‘]  WITH CHECK ADD  CONSTRAINT [chk_staging_‘+@tablename+‘] CHECK  ([PartitionRangeKey]>=‘ + @RangeKey1 + ‘ AND [PartitionRangeKey]<‘ + @RangeKey2 + ‘ AND [PartitionRangeKey] IS NOT NULL)‘)exec(‘ALTER TABLE [olap].[staging_‘+@tablename+‘] CHECK CONSTRAINT [chk_staging_‘+@tablename+‘]‘)exec(‘ALTER TABLE [olap].[‘+@tablename+‘] SWITCH PARTITION $PARTITION.[pf_‘+@tablename+‘] (‘+@RangeKey1+‘)  TO [olap].[staging_‘+@tablename+‘]‘)COMMIT TRANSACTIONend

declare @date datetimeset @date = convert(datetime,‘2016-09-23‘)print @datedeclare @RangeKey1 nvarchar(100) = convert(nvarchar(100), year(@date)*10000 + month(@date)*100 + day(@date))print @RangeKey1exec [olap].[usp_SwitchOutFact_Partition] @date,‘Fact_XM_XMBJXX‘