SQLServer批量创建表【结构相同，但表名不同】

CREATE PROCEDURE create_table
@tab_name varchar(20)--定义参数
AS
--declare @tablename varchar(10)
declare @i int --定义变量
set @i=1 --变量赋值
while @i<10
begin
--create table ss(id varchar(10) not null,name varchar(20),address varchar(20));
exec('create table  '+@tab_name+@i+'(
id nvarchar(10) not null,name nvarchar(20),address nvarchar(20)
)')
set @i = @i+1
END
GO

--执行存储过程

 exec create_table 'table_transflow_'

结果如下：

工作中使用的：

CREATE PROCEDURE create_table1
@tab_name nvarchar(20)--定义参数

AS
--declare @tablename varchar(10)
declare @i int --定义变量
set @i=10 --变量赋值
while @i<20
begin
--create table ss(id varchar(10) not null,address varchar(20));
exec(
'CREATE TABLE '+@tab_name+@i+'(
?? ?[ID] [decimal](18,0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,?? ?[TransNo] [nvarchar](12) NULL,?? ?[BuCode] [nvarchar](6) NULL,?? ?[BuName] [nvarchar](50) NULL,?? ?[PosNum] [nvarchar](10) NULL,?? ?[CardPrintNum] [nvarchar](10) NULL,?? ?[TrDate] [nvarchar](10) NULL,?? ?[TrTime] [nvarchar](10) NULL,?? ?[Amount] [money] NULL,?? ?[Fees] [money] NULL,?? ?[Type] [nvarchar](10) NULL,?? ?[ClearMarkCard] [nvarchar](1) NULL,?? ?[ClearMarkBu] [nvarchar](1) NULL,?? ?[ReMarks] [nvarchar](100) NULL,?? ?[LiSign] [nvarchar](10) NULL,?? ?[OffAmount] [money] NULL,?? ?[FullPrice] [money] NULL,?? ?[OPNum] [nvarchar](10) NULL,?CONSTRAINT [PK_'+@tab_name+@i+'] PRIMARY KEY CLUSTERED 
(
?? ?[ID] ASC
)WITH (PAD_INDEX? = OFF,STATISTICS_NORECOMPUTE? = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS? = ON,ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
?)ON [PRIMARY]



ALTER TABLE '+@tab_name+@i+' ADD? CONSTRAINT [DF_'+@tab_name+@i+'_Fees]? DEFAULT ((0)) FOR [Fees]


ALTER TABLE '+@tab_name+@i+' ADD? CONSTRAINT [DF_'+@tab_name+@i+'_ClearMarkCard]? DEFAULT ((0)) FOR [ClearMarkCard]


ALTER TABLE '+@tab_name+@i+' ADD? CONSTRAINT [DF_'+@tab_name+@i+'_ClearMarkBu]? DEFAULT ((0)) FOR [ClearMarkBu]

--默认值为10个0
ALTER TABLE '+@tab_name+@i+' ADD? CONSTRAINT [DF_'+@tab_name+@i+'_LiSign]? DEFAULT (((''0000000000''))) FOR [LiSign]


ALTER TABLE '+@tab_name+@i+' ADD? DEFAULT ((0)) FOR [OffAmount]


ALTER TABLE '+@tab_name+@i+' ADD? DEFAULT ((0)) FOR [FullPrice]')


set @i = @i+1
END
GO



--执行存储过程
 exec create_table1 'Tbl_Fz'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!