杯具的数据库结构,分享下

nianran520 2010-08-02 09:33:40 
/*====================================================*/

-- 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
...全文
171 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
hao1hao2hao3 2010-08-03
  • 打赏
  • 举报
 回复
果然是悲剧，太长了。
华夏小卒 2010-08-03
  • 打赏
  • 举报
 回复
临时表，系统表，存储过程，游标。。。。能用的全用了
htl258_Tony 2010-08-03
  • 打赏
  • 举报
 回复
Thank you to share
王向飞 2010-08-03
  • 打赏
  • 举报
 回复
可否大概解释下代码含义。。。。
华夏小卒 2010-08-03
  • 打赏
  • 举报
 回复
xiaoping04408 2010-08-03
  • 打赏
  • 举报
 回复
太长，的确不想看。
guguda2008 2010-08-02
  • 打赏
  • 举报
 回复
路过，太长了看不过来。
SQLCenter 2010-08-02
  • 打赏
  • 举报
 回复
暂时不看
sean_zhou 2010-08-02
  • 打赏
  • 举报
 回复
太长了不想看
duanzhi1984 2010-08-02
  • 打赏
  • 举报
 回复
复杂，真复杂。。。
duanzhi1984 2010-08-02
  • 打赏
  • 举报
 回复
BD............
MySQL数据库请求数据量太大崩溃了_分享：MySQL数据库崩溃解决过程
经常遇到这样的事情：莫名其妙关机，然后启动蓝屏，不能访问，最后决定重装系统。可惜这次的悲剧在于项目刚起步，暂且稳定，前段时间又一直忙于编码，对后台数据的备份...现把具体解决过程分享下吧：1、系统重装前，...
分享：MySQL数据库崩溃解决过程
　可惜这次的悲剧在于项目刚起步，暂且稳定，前段时间又一直忙于编码，对后台数据的备份没有太在意，导致数据库崩溃后只有上个月的备份数据。一直以为只要数据库装在d盘，c盘的覆盖不会有影响，结果恰恰是自己的不...
SQL Server 2005 Express 数据库导入到 oracle 10g的方法
 如果SQL Server数据库中有大量的表，我要导入的数据库中有四五十个，真是杯具。后来，查看浏览网页，发现需要安装SQL Server Express的工具包才能提供数据库的导入导出功能，下载地址为：http://g
数据库的垂直划分和水平划分
数据结构应用服务器OracleQQ算法 数据库的水平划分和垂直划分很早以前就接触了，只是没有实践，没有什么体会，只有最近两年才有接触，今天也和大家聊聊。 垂直划分 按照功能划分，把数据分...
安卓获取mysql数据封装方法_浅谈Android数据库CRUD操作的封装与实现（一）
Android系统内部集成了SQLite数据库，可是杯具的却没有Android系统可用的Hibernate。想当初做JavaEE时有Hibernate在手的那个飘逸，我尝试用JavaEE的思路封装了一个类似的工具包。(感谢“编程浪子”在JavaEE上给我的...
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章