批量修改数据库表字段类型
来源:互联网 发布:永利国际时时彩源码 编辑:程序博客网 时间:2024/04/29 10:13
实际使用例子:
/*--将所需表的某特定数值类型批量转换为其他类型--*/
/*--调用示例:E1T04_1996 char(10)-转换为nvarchar(100) --*/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[p_set]
as
declare tb cursor for
SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] nvarchar'
+' (100)' FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name='E1T04_1996' where b.name ='char' AND a.length='10'
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb INTO @sql
end
close tb
deallocate tb
--
--DECLARE @sql VARCHAR(1000)
--select @sql='alter table ['+d.name+'] alter column ['+a.name+'] nvarchar'
-- +'(100)' FROM syscolumns a
-- left join systypes b on a.xtype=b.xusertype
-- inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name='E1T04_1996' where b.name ='char' AND a.length='10'
--
-- PRINT @sql
--------------------------------------------------------------------------------------
批量修改数据库表字段类型
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_set]
GO
/*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar
--*/
/*--调用示例:
exec p_set
--*/
--修改的存储过程
create procedure p_set
as
declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] n'
+b.name+'('+cast(a.length*2 as varchar)+')'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.name in('char','varchar')
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
go
-- =======================================================
-- 批量更改某数据库中所有表中某字段的类型
-- =======================================================
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'sp_AlterColumnType'
)
DROP PROCEDURE sp_AlterColumnType
GO
CREATE PROCEDURE sp_AlterColumnType
AS
-- =============================================
-- 循环当前数据库中所有用户建的表
-- =============================================
DECLARE @TableName nvarchar(100)
DECLARE @TableID nvarchar(100)
DECLARE cursor_CustomTable CURSOR FOR
SELECT [name],[ID] FROM sysobjects where type ='U'
OPEN cursor_CustomTable
FETCH NEXT FROM cursor_CustomTable INTO @TableName,@TableID
WHILE @@FETCH_STATUS = 0
BEGIN
-- =============================================
-- 循环当前表中所有列,取出列名和列的类型
-- =============================================
DECLARE @ColumnName nvarchar(100)
DECLARE @ColumnType nvarchar(100)
DECLARE cursor_Column CURSOR FOR
select [name],xtype from syscolumns where [id] = @TableID and xtype=(select xtype from systypes where name='numeric')
OPEN cursor_Column
FETCH NEXT FROM cursor_Column INTO @ColumnName,@ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN
--在此处修改列类型为numeric(14,2)
exec('ALTER TABLE '+@TableName +' ALTER COLUMN '+@ColumnName +' numeric(14,2) null')
FETCH NEXT FROM cursor_Column INTO @ColumnName,@ColumnType
END
CLOSE cursor_Column
DEALLOCATE cursor_Column
-- 循环到下一个表.
FETCH NEXT FROM cursor_CustomTable INTO @TableName,@TableID
END
CLOSE cursor_CustomTable
DEALLOCATE cursor_CustomTable
GO
- 批量修改数据库表字段类型
- 批量修改数据库表字段类型
- 批量生成修改数据库表字段类型SQL脚本
- Oracle 批量修改表字段类型
- 修改数据库表字段
- 修改数据库表字段
- Teradata 修改表字段类型
- pgSql 修改表字段类型
- sqlserver2008数据库操作记录-修改表字段类型或长度
- 修改oracle数据库表字段类型,处理ORA-01439错误
- 数据库修改表字段类型和字段名
- 修改数据库表字段长度
- 修改表字段内容批量替换
- oracle 批量修改表字段脚本
- mysql批量修改表字段的属性
- Mysql批量修改表字段为大写。
- mysql数据库设计篇--表字段类型
- 数据库设计 之设计 表字段类型
- android:shape的使用
- ZQUOJ1403Factoring Large Numbers解题报告
- asdas
- 关于字符编码,你所需要知道的
- 使用频率最多的Linux命令
- 批量修改数据库表字段类型
- jquery绑定数据
- 安装Tomcat到Mac OSX( snow leopard)
- 【解决方案】Windows域用户不登录域如何自行修改密码
- .NET实现之(WebBrowser数据采集—基础篇)
- 怎样用word2007比较两篇文章的差异
- android下用数据库的一点小提示。
- CWaitCursor 光标设置为沙漏形状
- 2812 GPIO