/*====================================================*/
-- Author: 黄光伟
-- Create date: 2010-08-02 11:31:02
-- Description: 批量修改字段类型
-- 版本 MSSQL2000
-- 调用：UP_CHANGE_COLUMNTYPE 'CHAR','VARCHAR' --CHAR TO VARCHAR
/*====================================================*/
IF OBJECT_ID('UP_CHANGE_COLUMNTYPE','P') IS NOT NULL
DROP PROC UP_CHANGE_COLUMNTYPE
GO
CREATE PROC UP_CHANGE_COLUMNTYPE @TYPE_TO_CHANGE VARCHAR(50),--需修改类型
@TYPE VARCHAR(50) --修改后类型
AS
DECLARE @TABLENAME VARCHAR(50) --表名
DECLARE @SQL VARCHAR(8000),@EXEC VARCHAR(8000)
--索引临时表
CREATE TABLE #INDEX(INDEX_NAME VARCHAR(50),INDEX_DECLARE VARCHAR(500),INDEX_KEYS VARCHAR(300) )
--默认约束临时表
CREATE TABLE #DEFAULT(DEF_NAME VARCHAR(100),TYPE CHAR(1),COLUMNNAME VARCHAR(50),TEXT VARCHAR(8000))
--表字段临时表
CREATE TABLE #COLUMN(ID INT,COLUMNNAME VARCHAR(50),LENGTH INT,ISNULLABLE CHAR(1),CDEFAULT INT)
--外键临时表
CREATE TABLE #FOREIGN(FK_NAME VARCHAR(50),DISPLAYTABLE VARCHAR(50),COLNAME VARCHAR(50),DISPLAYCOL VARCHAR(50),LENGTH INT,ISNULLABLE CHAR(1) )
--定义参数
DECLARE @PKNAME VARCHAR(100)--主键名
DECLARE @PKFIELDNAME VARCHAR(500) --主键字段名
DECLARE @FK_NAME VARCHAR(50) --外键名
DECLARE @FOREIGNNAME VARCHAR(500) --外键字段名
DECLARE @DISPALYNAME VARCHAR(500) --外键对应字段名
DECLARE @DISPLAYTABLE VARCHAR(50) --外键对应表名
--返回未处理的表及字段
CREATE TABLE #RESULT(TABLENAME VARCHAR(50),COLUMNNAME VARCHAR(50))
--单字段处理
--DECLARE @DISPLAY VARCHAR(50),@DISPLAYCOL VARCHAR(50),@LENGTH INT ,@ISNULLABLE CHAR(1)
--生成需处理的表
SELECT H.NAME INTO #TEMP
FROM SYSOBJECTS H
WHERE H.XTYPE = 'U' AND EXISTS(SELECT 1 FROM SYSCOLUMNS T,SYSTYPES R
WHERE H.ID = T.ID AND
T.XUSERTYPE = R.XUSERTYPE AND
R.NAME = @TYPE_TO_CHANGE) AND
NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE FKEYID = H.ID
AND RKEYID IN (SELECT PARENT_OBJ FROM SYSOBJECTS WHERE XTYPE IN ('PK','D','C')))
ORDER BY CASE WHEN EXISTS(SELECT 1 FROM SYSFOREIGNKEYS WHERE FKEYID = H.ID) THEN 2 ELSE 1 END
--需手工处理
INSERT INTO #RESULT
SELECT OBJECT_NAME(ID),NAME
FROM SYSCOLUMNS H
WHERE EXISTS(SELECT 1 FROM SYSTYPES WHERE XUSERTYPE = H.XUSERTYPE
AND NAME = @TYPE_TO_CHANGE) AND EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE FKEYID = H.ID
AND RKEYID IN (SELECT PARENT_OBJ FROM SYSOBJECTS WHERE XTYPE IN ('PK','D','C')))
DECLARE CURSOR_TEMP CURSOR FOR
SELECT NAME FROM #TEMP
OPEN CURSOR_TEMP
FETCH CURSOR_TEMP INTO @TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
--初始化
SELECT @PKNAME = '',@PKFIELDNAME = '',@FK_NAME = '',@FOREIGNNAME = '',@DISPALYNAME = '',@DISPLAYTABLE = ''
SELECT @SQL = '',@EXEC = ''
--清空临时表
TRUNCATE TABLE #INDEX
TRUNCATE TABLE #COLUMN
TRUNCATE TABLE #FOREIGN
TRUNCATE TABLE #DEFAULT
--插入索引信息
INSERT INTO #INDEX
EXEC SP_HELPINDEX @TABLENAME
--插入需处理字段
INSERT INTO #COLUMN(ID,COLUMNNAME,LENGTH,ISNULLABLE,CDEFAULT)
SELECT T.COLID,T.NAME,T.LENGTH,T.ISNULLABLE,T.CDEFAULT
FROM SYSCOLUMNS T,SYSTYPES R,SYSOBJECTS H
WHERE T.XUSERTYPE = R.XUSERTYPE AND
T.ID = H.ID AND H.XTYPE = 'U' AND
R.NAME = @TYPE_TO_CHANGE AND
H.NAME = @TABLENAME
--判断主键是否存在需修改字段
IF EXISTS(SELECT 1 FROM #INDEX T,#COLUMN R WHERE CHARINDEX('primary key',T.INDEX_DECLARE) > 0 AND
CHARINDEX(','+R.COLUMNNAME+',',','+REPLACE(RTRIM(LTRIM(T.INDEX_KEYS)),', ',',')+',') > 0)
BEGIN
SELECT @PKNAME = T.INDEX_NAME,@PKFIELDNAME = T.INDEX_KEYS FROM
(
SELECT DISTINCT T.*
FROM #INDEX T ,#COLUMN R
WHERE CHARINDEX('primary key',T.INDEX_DECLARE) > 0 AND
CHARINDEX(','+R.COLUMNNAME+',',','+REPLACE(RTRIM(LTRIM(T.INDEX_KEYS)),', ',',')+',') > 0
) T
--删除主键
SET @SQL = 'ALTER TABLE ' + @TABLENAME + ' DROP CONSTRAINT ' + @PKNAME
PRINT @SQL + CHAR(13) + CHAR(10) + 'GO'
EXEC(@SQL)
END
--判断索引是否存在需修改字段
IF EXISTS(SELECT 1 FROM #INDEX T,#COLUMN R WHERE CHARINDEX('primary key',T.INDEX_DECLARE) = 0 AND
CHARINDEX(','+R.COLUMNNAME+',',','+REPLACE(RTRIM(LTRIM(T.INDEX_KEYS)),', ',',')+',') > 0)
BEGIN
SELECT @SQL = '',@EXEC = ''
SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + 'DROP INDEX DBO.' + @TABLENAME + '.' + T.INDEX_NAME + CHAR(13) + CHAR(10)
FROM
(
SELECT DISTINCT T.*
FROM #INDEX T ,#COLUMN R
WHERE CHARINDEX('primary key',T.INDEX_DECLARE) = 0 AND
CHARINDEX(','+R.COLUMNNAME+',',','+REPLACE(RTRIM(LTRIM(T.INDEX_KEYS)),', ',',')+',') > 0
) T
--删除索引
PRINT @SQL + CHAR(13) + CHAR(10) + 'GO'
EXEC(@SQL)
--索引语法
SELECT @EXEC = @EXEC + CHAR(13) + CHAR(10) + 'CREATE ' +
CASE CHARINDEX('unique',T.INDEX_DECLARE) WHEN 0 THEN 'INDEX ' ELSE 'UNIQUE INDEX ' END +
T.INDEX_NAME + ' ON ' + @TABLENAME + '(' + REPLACE(T.INDEX_KEYS,'(-)',' DESC ') + ') ON [PRIMARY]' + CHAR(13) + CHAR(10)
FROM
(
SELECT DISTINCT T.*
FROM #INDEX T ,#COLUMN R
WHERE CHARINDEX('primary key',T.INDEX_DECLARE) = 0 AND
CHARINDEX(','+R.COLUMNNAME+',',','+REPLACE(RTRIM(LTRIM(T.INDEX_KEYS)),', ',',')+',') > 0
) T
END
--生成默认约束临时表
INSERT INTO #DEFAULT
SELECT OBJECT_NAME(T.CDEFAULT),'D',T.COLUMNNAME,R.TEXT
FROM #COLUMN T,SYSCOMMENTS R
WHERE T.CDEFAULT = R.ID
UNION ALL
SELECT T.NAME,'C' AS TYPE,NULL,R.TEXT
FROM SYSOBJECTS T,SYSCOMMENTS R
WHERE T.PARENT_OBJ = OBJECT_ID(@TABLENAME,'U') AND
T.XTYPE = 'C' AND T.ID = R.ID
--删除默认约束
IF EXISTS(SELECT 1 FROM #DEFAULT)
BEGIN
SELECT @SQL = ''
SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + 'ALTER TABLE ' + @TABLENAME + ' DROP CONSTRAINT ' + DEF_NAME
FROM #DEFAULT
PRINT @SQL + CHAR(13) + CHAR(10) + 'GO'
EXEC(@SQL)
END
-- 生成外键临时表
INSERT INTO #FOREIGN
SELECT OBJECT_NAME(CONSTID),OBJECT_NAME(RKEYID),R.NAME,F.NAME,F.LENGTH,F.ISNULLABLE
FROM SYSFOREIGNKEYS T,SYSCOLUMNS R,SYSCOLUMNS F
WHERE T.FKEYID = R.ID AND T.FKEY = R.COLID AND
T.RKEYID = F.ID AND T.RKEY = F.COLID AND
OBJECT_NAME(FKEYID) = @TABLENAME AND
EXISTS(SELECT 1 FROM SYSFOREIGNKEYS,#COLUMN WHERE CONSTID = T.CONSTID AND FKEY = ID)
--判断外键是否存在需修改字段
IF EXISTS (SELECT 1 FROM #FOREIGN)
BEGIN
SELECT @SQL = ''
-- 刪除外键
SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + 'ALTER TABLE DBO.'+ @TABLENAME + ' DROP CONSTRAINT ' + FK_NAME
FROM (SELECT DISTINCT FK_NAME FROM #FOREIGN) T
PRINT @SQL+CHAR(13)+CHAR(10)+'GO'
EXEC(@SQL)
END
--修改字段类型
SELECT @SQL = ''
SELECT @SQL = @SQL + CHAR(13) + CHAR(10)
+ 'ALTER TABLE ' + @TABLENAME + ' ALTER COLUMN '+COLUMNNAME+' '+@TYPE+ '('+RTRIM(LENGTH)+') ' + CASE ISNULLABLE WHEN '1' THEN 'NULL' ELSE 'NOT NULL' END
FROM #COLUMN
PRINT @SQL+CHAR(13)+CHAR(10)+'GO'
EXEC(@SQL)
-- 创建主鍵
IF ISNULL(@PKFIELDNAME,'') <> ''
BEGIN
select @sql = ''
SET @SQL = 'ALTER TABLE ' + @TABLENAME + ' ADD CONSTRAINT ' + @PKNAME
+ ' PRIMARY KEY CLUSTERED(' + @PKFIELDNAME + ') ON [PRIMARY]'
PRINT @SQL + CHAR(13) + CHAR(10) + 'GO'
EXEC(@SQL)
END
--重建索引
IF ISNULL(@EXEC,'') <> ''
BEGIN
PRINT @EXEC + CHAR(13) + CHAR(10) + 'GO'
EXEC(@EXEC)
SELECT @EXEC = ''
END
--重建默认约束
--ALTER TABLE [dbo].[CT_ORDER_AUDIT_SYNDIC] ADD CONSTRAINT [DF__CT_ORDER___If_Au__16E43C86] DEFAULT ('1') FOR [If_Audit]
IF EXISTS(SELECT 1 FROM #DEFAULT)
BEGIN
SELECT @SQL = ''
SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + 'ALTER TABLE ' + @TABLENAME + ' ADD CONSTRAINT ' + DEF_NAME + CASE TYPE WHEN 'C' THEN ' CHECK ' + TEXT ELSE ' DEFAULT ' + TEXT + ' FOR ' + COLUMNNAME END
FROM #DEFAULT
PRINT @SQL + CHAR(13) + CHAR(10) + 'GO'
EXEC(@SQL)
END
--重建外键
/*
CFV_FK CAR_FOR_VCH ACC_NO ACC_NO 0
CFV_FK CAR_FOR_VCH CAR_FOR_CODE CAR_FOR_CODE 0
CFV_FK CAR_FOR_VCH CAR_FOR_ID CAR_FOR_ID 0
ALTER TABLE [dbo].[wrkshop_check] ADD CONSTRAINT [wrk_mat_code] FOREIGN KEY
(
[mat_code]
) REFERENCES [MAT_MASTER] (
[MAT_CODE]
)
--CREATE TABLE #FOREIGN(FK_NAME VARCHAR(50),DISPLAYTABLE VARCHAR(50),COLNAME VARCHAR(50),DISPLAYCOL VARCHAR(50),ISNULLABLE CHAR(1) )
*/
IF EXISTS(SELECT 1 FROM #FOREIGN)
BEGIN
SELECT @SQL = ''
SELECT @SQL = @SQL + CHAR(13) + CHAR(10) + 'ALTER TABLE ' + DISPLAYTABLE + ' ALTER COLUMN ' + DISPLAYCOL + ' ' + @TYPE + '('+RTRIM(LENGTH)+') ' + CASE ISNULLABLE WHEN '1' THEN 'NULL' ELSE 'NOT NULL' END
FROM #FOREIGN T
WHERE T.COLNAME IN (SELECT COLUMNNAME FROM #COLUMN)
PRINT @SQL+CHAR(13)+CHAR(10)+'GO'
EXEC(@SQL)
DECLARE CURSOR_FOREIGN CURSOR FOR
SELECT DISTINCT FK_NAME FROM #FOREIGN
OPEN CURSOR_FOREIGN
FETCH CURSOR_FOREIGN INTO @FK_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ''
SELECT TOP 1 @DISPLAYTABLE = DISPLAYTABLE FROM #FOREIGN WHERE FK_NAME = @FK_NAME
SELECT @FOREIGNNAME =@FOREIGNNAME + COLNAME + ',',
@DISPALYNAME =@DISPALYNAME + DISPLAYCOL + ','
FROM #FOREIGN WHERE FK_NAME = @FK_NAME
SELECT @FOREIGNNAME = LEFT(@FOREIGNNAME,LEN(@FOREIGNNAME) - 1),
@DISPALYNAME = LEFT(@DISPALYNAME,LEN(@DISPALYNAME) - 1)
SELECT @SQL = 'ALTER TABLE ' + @TABLENAME + ' ADD CONSTRAINT ' + @FK_NAME + ' FOREIGN KEY
(' + @FOREIGNNAME + ') REFERENCES ' + @DISPLAYTABLE + '(' +
@DISPALYNAME + ')'
PRINT @SQL+CHAR(13)+CHAR(10)+'GO'
EXEC(@SQL)
FETCH NEXT FROM CURSOR_FOREIGN INTO @FK_NAME
END
CLOSE CURSOR_FOREIGN
DEALLOCATE CURSOR_FOREIGN
END
IF @@ERROR > 0
BEGIN
ROLLBACK TRAN
CLOSE CURSOR_TEMP
DEALLOCATE CURSOR_TEMP
DROP TABLE #INDEX,#COLUMN,#TEMP,#FOREIGN,#DEFAULT
END
ELSE
BEGIN
PRINT '-----------------------------'
COMMIT TRAN
FETCH NEXT FROM CURSOR_TEMP INTO @TABLENAME
END
END
CLOSE CURSOR_TEMP
DEALLOCATE CURSOR_TEMP
--返回未处理的表及字段
SELECT * FROM #RESULT
DROP TABLE #INDEX,#COLUMN,#TEMP,#FOREIGN,#DEFAULT,#RESULT
本版专家分：14917结帖率 100%
本版专家分：30691
本版专家分：140
本版专家分：127677
