/*在修改列类型时，提示有依赖，如下。现提供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>
					
				                
                                

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。