SQL 文件以及文件组

1.SQL Server根据分区表名查找所在的文件及文件组实现脚本

--SQL Server根据分区表名查找所在的文件及文件组实现脚本 
SELECT  
          fg.name AS FileGroupName ,
          fg.name ,
          t.name ,
          f.name AS FILENAME
  FROM    sys.tables AS t
          INNER JOIN sys.indexes AS i ON ( t.object_id = i.object_id )
          INNER JOIN sys.filegroups AS fg ON i.data_space_id = fg.data_space_id
          INNER JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
 WHERE   t.name = 'test1'

-- 同上 test1为表名称

SELECT a.* FROM sys.filegroups a
LEFT JOIN sys.database_files b ON a.data_space_id = b.data_space_id
LEFT JOIN sys.indexes c ON c.data_space_id = b.data_space_id
WHERE c.object_id = OBJECT_ID('test1')

2. 如何更改一个表所在的表分区   How I can move table to another filegroup

第一种方式,就是开始用的那种重新创建表,然后重新建立相关索引

参考:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/025825ee-ecbc-4d7c-94ff-0595afc48347/how-to-move-a-table-from-a-file-groupprimary-to-another?forum=transactsql

 

第二种方式,就是只创建索引或聚集索引

CREATE UNIQUE  CLUSTERED INDEX PK_ta -- PK_ta   原表的索引名称
   ON tableName(id) -- 表名称 id 为主键,所以用了UNIQUE, 如果是有其它非聚集索引则不需要这个关键字
   WITH DROP_EXISTING
   ON db_fg2-- 文件组名称

-- 查看表索引
  EXEC sp_helpindex 'table'

 

You do not need to drop the current clustered index, instead use the option "with (DROP_EXISTING = ON)" in 2005 or "with DROP_EXISTING" in 2000. Notice that the approach will be different if the clustered index is related to a primary key or unique constraint.

eg:

If you want to just move the table to a new filegroup, you need to recreate the clustered index on the table (after all: the clustered index is the table data) on the new filegroup you want.

You can do this with e.g.:

CREATE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

or if your clustered index is unique:

CREATE UNIQUE CLUSTERED INDEX CIX_YourTable
   ON dbo.YourTable(YourClusteringKeyFields)
   WITH DROP_EXISTING
   ON [filegroup_name]

3.如何处理MSSQL 同步时的外键约束问题

--获得禁用所有外键约束的语句

select 'alter table '+quotename(object_Name(parent_obj))+' oncheck constraint '+quotename(Name)+';'+char(13) 
from sysobjects where Xtype='F'
--获得启用所有外键约束的语句

select 'alter table '+quotename(object_Name(parent_obj))+' check constraint '+quotename(Name)+';'+char(13) 
from sysobjects where Xtype='F'

4.

--未完待续