自动建表的存储过程

 自动建表的存储过程  
最近做项目，需要每月自动建一个月份表，于是找资料如何自动建表，原来用存储过程可以做到自动建表
下面是我写的例子
create Proc [dbo].[CreateMonthTable]
@TableName varchar(30)
AS
Begin
Declare @Count int
Declare @StrSql nvarchar(2000)
Declare @CreateSQL varchar(max)
Set @StrSql=N'Select @InsideCount=Count(*) From SysObjects Where Id = object_id('''+ @TableName +''') And Type = ''U'''
  Exec Sp_ExecuteSQL @StrSql ,N'@InsideCount int output',@Count output
IF(@Count = 0)
  Begin
   Set @CreateSQL='Create Table '+ @TableName +
       '(id int IDENTITY(1, 1) not null Primary Key,
       UserName nvarchar(20) not null,
       Address nvarchar(100),
       StatusName varchar(50),
       AddTime datetime default getdate())'
   Exec(@CreateSQL)
  End
End
语句里，Select @InsideCount=Count(*) From SysObjects Where Id = object_id('''+ @TableName +''') And Type = ''U''这个是用于判断数据库是否已存在这个表，如果有的话就不再次创建了
执行存储过程的话用exec CreateMonthTable 'onemonth'
大家是不是觉得写得有点死，希望字段也能够在外面传入，其实改良一下就行
create  Proc  CreateMonthTable
@TableName varchar(30),
@Fields varchar(max)
AS
Begin
Declare @Count int
  Declare @StrSql nvarchar(2000)
Declare @CreateSQL varchar(max)
Set @StrSql=N'Select @InsideCount=Count(*) From SysObjects Where Id = object_id('''+ @TableName +''') And Type = ''U'''
  Exec Sp_ExecuteSQL @StrSql ,N'@InsideCount int output',@Count output
  IF(@Count = 0)
  Begin
   Set @CreateSQL='Create Table '+ @TableName + @Fields
   Exec(@CreateSQL)
  End
End
执行存储过程时要注意的是字段前后要用()括起来，要不然会报错
如:exec CreateMonthTable 'twomonth','(id int IDENTITY(1, 1) not null Primary Key,UserName nvarchar(20) not null,Address nvarchar(100),StatusName varchar(50),AddTime datetime default getdate())'
如果觉得麻烦，可以再必改写一下，把 Set @CreateSQL='Create Table '+ @TableName + @Fields改写成:
Set @CreateSQL='Create Table '+ @TableName + +'([email='+@Fields+')']'+@Fields+')'[/email]
那么在执行存储过程时就不需要在字段前后加()了

如下：exec CreateMonthTable 'twomonth','id int IDENTITY(1, 1) not null Primary Key,UserName nvarchar(20) not null,Address nvarchar(100),StatusName varchar(50),AddTime datetime default getdate()'