{"id":1934,"date":"2012-02-23T01:28:43","date_gmt":"2012-02-23T01:28:43","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1934"},"modified":"2012-02-24T06:12:58","modified_gmt":"2012-02-24T06:12:58","slug":"%e4%bf%ae%e6%94%b9%e5%88%97%e7%b1%bb%e5%9e%8b%e5%a4%8d%e5%88%b6%e7%b4%a2%e5%bc%95","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/02\/23\/%e4%bf%ae%e6%94%b9%e5%88%97%e7%b1%bb%e5%9e%8b%e5%a4%8d%e5%88%b6%e7%b4%a2%e5%bc%95\/","title":{"rendered":"\u4fee\u6539\u5217\u7c7b\u578b\u590d\u5236\u7d22\u5f15"},"content":{"rendered":"
\n
\/*\u5728\u4fee\u6539\u5217\u7c7b\u578b\u65f6\uff0c\u63d0\u793a\u6709\u4f9d\u8d56\uff0c\u5982\u4e0b\u3002\u73b0\u63d0\u4f9bAlterColumnType\u5b58\u50a8\u8fc7\u7a0b\u7528\u6765\u590d\u5236\u8868\u4f9d\u8d56\u5173\u7cfb\u3002
\nALTER TABLE dbo.bmdCustomerTel ALTER COLUMN CustomerID INT
\n\u6d88\u606f 5074\uff0c\u7ea7\u522b 16\uff0c\u72b6\u6001 1\uff0c\u7b2c 1 \u884c
\n\u7d22\u5f15'IX_bmdCustomerTel_2' \u4f9d\u8d56\u4e8e \u5217'CustomerID'\u3002
\n\u6d88\u606f 4922\uff0c\u7ea7\u522b 16\uff0c\u72b6\u6001 9\uff0c\u7b2c 1 \u884c
\n\u7531\u4e8e\u4e00\u4e2a\u6216\u591a\u4e2a\u5bf9\u8c61\u8bbf\u95ee\u6b64\u5217\uff0cALTER TABLE ALTER COLUMN CustomerID \u5931\u8d25\u3002
\n*
\n--\u4e3b\u952e\uff1aSELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='\u8868\u540d'<\/p>\n
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AlterColumnType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
\ndrop procedure [dbo].[AlterColumnType]
\nGO<\/p>\n
\/*\u4fee\u6539\u5217\u7c7b\u578b\u65f6\uff0c\u81ea\u52a8\u67e5\u627e\u76f8\u5173\u7d22\u5f15\uff0c\u5220\u9664\u518d\u91cd\u5efa*\/
\nCREATE       PROCEDURE [dbo].[AlterColumnType]
\n@TableName VARCHAR(50),
\n@ColumnName VARCHAR(50),
\n@NewColumnType VARCHAR(1000)
\nAS<\/p>\n
SELECT  t.object_id,ix.type_desc,t.name TableName,IX.index_id,ix.name indexName,s.name SchemeName INTO #
\nFROM  SYS.tables t
\nJOIN SYS.indexes IX ON t.[object_id] = IX.[object_id]
\nJOIN SYS.schemas s ON S.[schema_id] = t.[schema_id] --\u8868\u67b6\u6784\u540d
\nWHERE  t.object_id = object_id(@TableName)
\nAND EXISTS(  --\u62e5\u6709\u6b64\u5217\u7684\u7d22\u5f15
\n\tSELECT 1 FROM sys.index_columns cl
\n\tJOIN sys.columns c ON cl.column_id = c.column_id AND c.object_id = cl.object_id
\n\tWHERE c.name = @ColumnName AND cl.index_id = ix.index_id AND cl.object_id = t.object_id )<\/p>\n
DECLARE cur_dx CURSOR FOR
\nSELECT  ' DROP INDEX ' + QUOTENAME(indexName) + '  ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName) --t.name,ix.name,ix.type_desc,cl.name
\nFROM  # t<\/p>\n
UNION ALL<\/p>\n
SELECT ' ALTER TABLE ' + @TableName + ' ALTER COLUMN  '+ @ColumnName + ' ' + @NewColumnType<\/p>\n
UNION ALL<\/p>\n
SELECT ' CREATE ' + type_desc collate Chinese_PRC_CI_AS_WS + ' INDEX ' + QUOTENAME(indexName) + '  ON ' + QUOTENAME(SchemeName) + '.' + QUOTENAME(TableName)
\n+  '(' + STUFF( --\u7d22\u5f15\u5217\uff0c\u590d\u5408\u7d22\u5f15
\n\t(SELECT ',' + CONVERT(NVARCHAR(100),cl.name)
\n\tFROM sys.index_columns c
\n\tJOIN sys.columns cl ON cl.column_id = c.column_id AND c.object_id = cl.object_id
\n\tWHERE c.object_id = t.object_id AND index_id = t.index_id
\n\tORDER BY key_ordinal
\n\tFOR XML PATH('')),
\n1,1,'') + ')'
\nFROM #  t<\/p>\n
DECLARE @sql NVARCHAR(max)
\nOPEN cur_dx
\nFETCH cur_dx INTO @sql
\nWHILE @@FETCH_STATUS = 0
\nBEGIN
\n\tEXEC sp_executesql @sql
\n\tRAISERROR('Executed:%s',10,1,@sql) WITH NOWAIT
\n\t--PRINT @SQL
\n\tFETCH cur_dx INTO @sql
\nEND
\nCLOSE cur_dx
\nDEALLOCATE cur_dx<\/p>\n
DROP TABLE #<\/p>\n
\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"
\/*\u5728\u4fee\u6539\u5217\u7c7b\u578b\u65f6\uff0c\u63d0\u793a\u6709\u4f9d\u8d56\uff0c\u5982\u4e0b\u3002\u73b0\u63d0\u4f9bAlterColumnType\u5b58\u50a8\u8fc7\u7a0b\u7528\u6765\u590d\u5236\u8868\u4f9d\u8d56\u5173\u7cfb\u3002 ALT […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-1934","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=1934"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1934\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}