自动建表 2

最新推荐文章于 2022-04-16 17:05:29 发布

原创最新推荐文章于 2022-04-16 17:05:29 发布 · 615 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #数据库 #output #object #image #table

sql 专栏收录该内容

90 篇文章

订阅专栏

/*
declare @Name varchar(20)
set @Name = ''
exec dbo.p_CreateYXRZTable @s_tablename='WWdcctrl12',@int=30,@dec=5,@strSm=0,@strMi=0,@strBig=0,@text=0,@message = @Name out
select @Name

CREATE proc dbo.p_CreateYXRZTable
@s_tablename varchar(100),
@int int = null,
@dec int = null,
@strSm int = null,
@strMi int = null,
@strBig int = null,
@text int = null,
@Message int=null Output
as
/*---------------------------------------------------------------------------------------------------------------
----------过程传入的是一种数据类型的个数,@s_用来存储新增列的信息
---------------------------------------------------------------------------------------------------------------*/
declare @s_int varchar(8000),@s_Decimal varchar(8000),@s_StrSm varchar(8000),@s_StrMi varchar(8000),@s_StrBig varchar(8000),@s_Text varchar(8000),@s_defultB varchar(1000),@s_defultE varchar(1000)
declare @i int,@iFlag int
select @s_Decimal = '',@s_int = '',@s_StrSm = '',@s_StrMi = '',@s_StrBig = '',@s_Text = '',@s_defultB = '',@s_defultE = ''
select @int = isnull(@int,0),@dec = isnull(@dec,0),@strSm = isnull(@strSm,0),@strMi = isnull(@strMi,0),@Text = isnull(@Text,0),@strBig = isnull(@strBig,0)

--生成数据表名称
set @s_tablename='tbYXRZ' + @s_tablename
--判断目标数据库是否已经存在要生成的数据表,在没有重名的情况下进行新加表处理
if not exists(select 1 from sysobjects where id=object_id(@s_tablename) and type='u')
begin
--表头表尾字段处理
set @s_defultB = 'CREATE TABLE ['+ @s_tablename +'] (
[yxID] [int] IDENTITY (1, 1) NOT NULL ,
[reportDate] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[jz] [int] NULL ,
[bz] [int] NULL ,
[bc] [int] NULL ,
[tq] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[zbry] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[jjman1] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[jjtime1] [datetime] NULL ,
[jjman2] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[jjtime2] [datetime] NULL ,
[CreateTime] [datetime] NULL ,
[modifyman] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[modifyTime] [datetime] NULL ,
[Data] [image] NULL ,
[yxjs] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[jdsx] [text] COLLATE Chinese_PRC_CI_AS NULL ,'

set @s_defultE = 'CONSTRAINT [PK_'+@s_tablename+'] PRIMARY KEY CLUSTERED
(
[yxID] DESC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
--------------------
--数据表字段语句生成
--------------------
set @i=1
while @int >= @i begin
set @s_int = @s_int + '[Int' + right(1000 + @i,3) + '] [int] NULL ,'
set @i = @i + 1
end

set @i=1
while @dec >= @i begin
set @s_Decimal = @s_Decimal + '[Dec' + right(1000 + @i,3) + '] [decimal](18, 4) NULL ,'
set @i = @i + 1
end

set @i=1
while @strSm >= @i begin
--set @s_StrSm = @s_StrSm + '[StrSm' + right(1000 + @i,3) + '] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,'
set @s_StrSm = @s_StrSm + '[StrSm' + right(1000 + @i,3) + '] [varchar] (10) ,'
set @i = @i + 1
end

set @i=1
while @strMi >= @i begin
--set @s_StrMi = @s_StrMi + '[StrMi' + right(1000 + @i,3) + '] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,'
set @s_StrMi = @s_StrMi + '[StrMi' + right(1000 + @i,3) + '] [varchar] (50) ,'
set @i = @i + 1
end

set @i=1
while @strBig >= @i begin
--set @s_StrBig = @s_StrBig + '[StrBig' + right(1000 + @i,3) + '] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,'
set @s_StrBig = @s_StrBig + '[StrBig' + right(1000 + @i,3) + '] [varchar] (500) ,'
set @i = @i + 1
end

set @i=1
while @text >= @i begin
set @s_Text = @s_Text + '[Text' + right(1000 + @i,3) + '] [text] COLLATE Chinese_PRC_CI_AS NULL ,'
set @i = @i + 1
end
--------------------
--数据表创建
--------------------
exec(@s_defultB + @s_int + @s_Decimal + @s_StrSm + @s_StrMi + @s_StrBig + @s_Text + @s_defultE)
end
else
--@message=1表示数据库中已经存在同名数据表
set @message=1