Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перенос таблицы в другой файл -- оцените мой скрипт  [new]
Андрей Усачёв
Member

Откуда: Рига, Латвия
Сообщений: 128
Потребовалось перенести таблицы в другой файл. Нигде в интернете не нашёл полного готового решения, поэтому пришлось потратить полдня и написать своё. Пожалуйста, помогите найти возможные ошибки и недочёты.

-- This Microsoft SQL Server SELECT statement creates a script that moves the specified tables to the specified filegroup (physical data file)
select
	i.table_name,
	sql =
		-- Progress indicator
		'print ''' + convert(varchar, row_number() over(order by i.table_name, i.index_id)) + ' / ' + convert(varchar, count(*) over()) + '''; ' +

		-- Creating a dummy identity column if the table has neither an unique index or an identity column
		case when i.columns_from_index_id is null and i.identity_column_name is null then
			'alter table ' + i.table_name + ' add DummyIdentityColumn int identity(1, 1); ' else '' end +

		-- Moving a table means moving all it's indexes
		-- The table data itself is the index with
		-- 1) index_id = 0, type = 0 and type_desc = 'HEAP' (if it doesn't have a clustered index) or
		-- 2) index_id = 1, type = 1 and type_desc = 'CLUSTERED' (if it does)
		'create ' +
		case
			-- To move the data of a table with no clustered key, we create a dummy clustered index and drop it afterwards
			when i.type_desc = 'HEAP' then 'clustered index DummyClusteredIndex '
			-- Recreating an existing index
			else
				case when i.is_unique = 1 then 'unique ' else '' end +
				i.type_desc collate database_default + ' ' +
				'index [' + i.name + '] '
		end +
		'on ' + i.table_name + ' (' +
			coalesce(
				-- When recreating an existing index, this is it's column list
				-- When creating a dummy clustered index, this is the first unique index/constraint column list of the same table,
				substring((
					select
						', ' + c.name + case when ic.is_descending_key = 1 then ' desc' else '' end
					from
						sys.index_columns as ic
						inner join sys.columns as c on ic.object_id = c.object_id and ic.column_id = c.column_id
					where
						ic.object_id = i.object_id and
						ic.index_id = i.columns_from_index_id and
						ic.is_included_column = 0
					order by
						ic.key_ordinal
					for xml path('')
				), 3, 9999),
				-- or the first identity column name, 
				i.identity_column_name,
				-- or the dummy identity column name we've just created 
				'DummyIdentityColumn'
			) +
		')' + 
		case
			-- Creating a dummy clustered index with no properties
			when i.type_desc = 'HEAP' then ''
			-- Recreating an existing index with all it's properties
			else
				isnull(
					' include (' +
					SubString((
						select
							', ' + c.name
						from
							sys.index_columns as ic
							inner join sys.columns as c on ic.object_id = c.object_id and ic.column_id = c.column_id
						where
							ic.object_id = i.object_id and
							ic.index_id = i.columns_from_index_id and
							ic.is_included_column = 1
						order by
							ic.key_ordinal
						for xml path('')
					), 3, 9999) +
					')'
				, '') +
				isnull(' where ' + i.filter_definition, '') +
				' with (' +
				'pad_index = ' + case when i.is_padded = 1 then 'on' else 'off' end + ', ' +
				'statistics_norecompute = ' + case when st.no_recompute = 1 then 'on' else 'off' end + ', ' +
				'sort_in_tempdb = on, ' +
				'drop_existing = on, ' +
				'online = off, ' +
				'allow_row_locks = ' + case when i.allow_row_locks = 1 then 'on' else 'off' end + ', ' +
				'allow_page_locks = ' + case when i.allow_page_locks = 1 then 'on' else 'off' end + ', ' +
				'fillfactor = ' + convert(varchar, case when i.fill_factor = 0 then 100 else i.fill_factor end) + ', ' +
				'ignore_dup_key = ' + case when i.ignore_dup_key = 1 then 'on' else 'off' end +
			')'
		end +
		' on [SECONDARY]; ' + -- New filegroup name

		-- Dropping the dummy clustered index, if we have created one
		case when i.type_desc = 'HEAP' then 'drop index DummyClusteredIndex on ' + i.table_name + '; ' else '' end +

		-- Dropping the dummy identity column, if we have created one
		case when i.columns_from_index_id is null and i.identity_column_name is null then
			'alter table ' + i.table_name + ' drop column DummyIdentityColumn; ' else '' end
from
	(
	select
		table_name = '[' + s.name + '].[' + t.name + ']',
		-- For an existing index, this is it's index_id
		-- For HEAP type index (for tables without a clustered index), this is the first unique index/constraint index_id
		columns_from_index_id =
			case
				when i.type_desc = 'HEAP' then
					(select min(index_id) from sys.indexes where object_id = t.object_id and (is_unique = 1 or is_unique_constraint = 1))
				else i.index_id
			end,
		-- The first identity column name, as we may need it to create a dummy clustered index
		identity_column_name = (select min(name) from sys.columns where object_id = t.object_id and (is_identity = 1 or is_rowguidcol = 1)),
		i.*
	from
		sys.tables as t
		inner join sys.schemas as s on t.schema_id = s.schema_id
		inner join sys.indexes as i on t.object_id = i.object_id
		inner join sys.filegroups as f on i.data_space_id = f.data_space_id
	where
		t.name = 'CustTable' and -- Table(-s) to move
		f.name != 'SECONDARY' -- New filegroup name
	) as i
	left join sys.stats as st on i.object_id = st.object_id and i.index_id = st.stats_id
order by
	i.table_name,
	i.index_id


К сообщению приложен файл (move_table(s)_to_another_filegroup.sql - 5Kb) cкачать
8 мар 15, 21:34    [17359172]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить