修改列类型复制索引


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

/*在修改列类型时,提示有依赖,如下。现提供AlterColumnType存储过程用来复制表依赖关系。
ALTER TABLE dbo.bmdCustomerTel ALTER COLUMN CustomerID INT
消息 5074,级别 16,状态 1,第 1 行
索引'IX_bmdCustomerTel_2' 依赖于 列'CustomerID'。
消息 4922,级别 16,状态 9,第 1 行
由于一个或多个对象访问此列,ALTER TABLE ALTER COLUMN CustomerID 失败。
*
--主键:SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AlterColumnType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AlterColumnType]
GO

/*修改列类型时,自动查找相关索引,删除再重建*/
CREATE PROCEDURE [dbo].[AlterColumnType]
@TableName VARCHAR(50),
@ColumnName VARCHAR(50),
@NewColumnType VARCHAR(1000)
AS

SELECT t.object_id,ix.type_desc,t.name TableName,IX.index_id,ix.name indexName,s.name SchemeName INTO #
FROM SYS.tables t
JOIN SYS.indexes IX ON t.[object_id] = IX.[object_id]
JOIN SYS.schemas s ON S.[schema_id] = t.[schema_id] --表架构名
WHERE t.object_id = object_id(@TableName)
AND EXISTS( --拥有此列的索引
SELECT 1 FROM sys.index_columns cl
JOIN sys.columns c ON cl.column_id = c.column_id AND c.object_id = cl.object_id
WHERE c.name = @ColumnName AND cl.index_id = ix.index_id AND cl.object_id = t.object_id )

DECLARE cur_dx CURSOR FOR
SELECT ' DROP INDEX ' + QUOTENAME(indexName) + ' ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName) --t.name,ix.name,ix.type_desc,cl.name
FROM # t

UNION ALL

SELECT ' ALTER TABLE ' + @TableName + ' ALTER COLUMN '+ @ColumnName + ' ' + @NewColumnType

UNION ALL

SELECT ' CREATE ' + type_desc collate Chinese_PRC_CI_AS_WS + ' INDEX ' + QUOTENAME(indexName) + ' ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName)
+ '(' + STUFF( --索引列,复合索引
(SELECT ',' + CONVERT(NVARCHAR(100),cl.name)
FROM sys.index_columns c
JOIN sys.columns cl ON cl.column_id = c.column_id AND c.object_id = cl.object_id
WHERE c.object_id = t.object_id AND index_id = t.index_id
ORDER BY key_ordinal
FOR XML PATH('')),
1,1,'') + ')'
FROM # t

DECLARE @sql NVARCHAR(max)
OPEN cur_dx
FETCH cur_dx INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @sql
RAISERROR('Executed:%s',10,1,@sql) WITH NOWAIT
--PRINT @SQL
FETCH cur_dx INTO @sql
END
CLOSE cur_dx
DEALLOCATE cur_dx

DROP TABLE #

/pre>