已有SQLSERVER表怎么生成其代码

CREATE PROCEDURE dbo.OutputData     @tablename   sysname     AS     declare    @column   varchar(1000)     declare    @columndata   varchar(1000)     declare    @sql   varchar(4000)     declare    @xtype   tinyint     declare    @name   sysname     declare    @objectId   int     declare    @objectname   sysname     declare    @ident   int     set   nocount   on     set    @objectId=object_id(@tablename)     if @objectId   is   null   --   判断对象是否存在       begin           print @tablename + '对象不存在'           return         end     set @objectname=rtrim(object_name(@objectId))     if @objectname is null or charindex(@objectname,@tablename)=0    begin           print @tablename + '对象不在当前数据库中'            return         end        if OBJECTPROPERTY(@objectId,'IsTable')   <   >   1   --   判断对象是否是表        begin           print @tablename + '对象不是表'           return         end        select    @ident=status&0x80   from    syscolumns   where    [email protected]   and    status&0x80=0x80          if @ident is   not   null         print    'SET   IDENTITY_INSERT   '+ @TableName + '   ON'     --定义游标，循环取数据并生成Insert语句declare syscolumns_cursor cursor for     select    c.name,c.xtype   from    syscolumns   c           where    [email protected]           order   by    c.colid     --打开游标open    syscolumns_cursor     set @column=''     set @columndata=''     fetch   next   from    syscolumns_cursor   into    @name,@xtype     while    @@fetch_status   <> -1         begin         if    @@fetch_status   <> -2           begin           if    @xtype   not   in(189,34,35,99,98)   --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理            begin             set    @[email protected] +           case   when   len(@column)=0   then ''                   else    ','                 end + @name             set    @columndata = @columndata +           case   when   len(@columndata)=0   then    ''                    else    ','','','                 end +           case   when @xtype   in(167,175) then    [email protected]+'+'''''''''                --varchar,char                      when    @xtype   in(231,239)   then    [email protected]+'+'''''''''             --nvarchar,nchar                      when    @xtype=61   then    '''''''''+convert(char(23),[email protected]+',121)+'''''''''   --datetime                      when    @xtype=58   then    '''''''''+convert(char(16),[email protected]+',120)+'''''''''   --smalldatetime                       when    @xtype=36   then    '''''''''+convert(char(36),[email protected]+')+'''''''''       --uniqueidentifier                      else    @name                    end             end           end         fetch   next   from    syscolumns_cursor   into    @name,@xtype         end     close    syscolumns_cursor     deallocate    syscolumns_cursor          set @sql='set   nocount   on   select   ''insert   [email protected]+'([email protected]+')   values(''as   ''--'',[email protected]+','')''   from   [email protected]         print    [email protected]     exec(@sql)          if    @ident   is   not   null     print 'SET   IDENTITY_INSERT   [email protected]+'   OFF'
------解决方案--------------------
SQL code
--只能生成insert 脚本create procedure [dbo].[sp_generate_insert_script]@tablename_mask varchar(30) = NULLasbegindeclare @tablename varchar (128)declare @tablename_max varchar (128)declare @tableid intdeclare @columncount numeric (7,0)declare @columncount_max numeric (7,0)declare @columnname varchar (30)declare @columntype intdeclare @string varchar (30)declare @leftpart varchar (8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */declare @rightpart varchar (8000) /* without having to resort to concatenation */declare @hasident intset nocount on-- take ALL tables when no mask is given (!)if (@tablename_mask is NULL)beginselect @tablename_mask = '%'end-- create table columninfo now, because it will be used several timescreate table #columninfo(num numeric (7,0) identity,name varchar(30),usertype smallint)select name,idinto #tablenamesfrom sysobjectswhere type in ('U' ,'S')and name like @tablename_mask-- loop through the table #tablenamesselect @tablename_max = MAX (name),@tablename = MIN (name)from #tablenameswhile @tablename <= @tablename_maxbeginselect @tableid = idfrom #tablenameswhere name = @tablenameif (@@rowcount <> 0)begin-- Find out whether the table contains an identity columnselect @hasident = max( status & 0x80 )from syscolumnswhere id = @tableidtruncate table #columninfoinsert into #columninfo (name,usertype)select name, typefrom syscolumns Cwhere id = @tableidand type <> 37 -- do not include timestamps-- Fill @leftpart with the first part of the desired insert-statement, with the fieldnamesselect @leftpart = 'select ''insert into [email protected]select @leftpart = @leftpart + '('select @columncount = MIN (num),@columncount_max = MAX (num)from #columninfowhile @columncount <= @columncount_maxbeginselect @columnname = name,@columntype = usertypefrom #columninfowhere num = @columncountif (@@rowcount <> 0)beginif (@columncount < @columncount_max)beginselect @leftpart = @leftpart + @columnname + ','endelsebeginselect @leftpart = @leftpart + @columnname + ')'endendselect @columncount = @columncount + 1endselect @leftpart = @leftpart + ' values('''-- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formattedselect @columncount = MIN (num),@columncount_max = MAX (num)from #columninfoselect @rightpart = ''while @columncount <= @columncount_maxbeginselect @columnname = name,@columntype = usertypefrom #columninfowhere num = @columncountif (@@rowcount <> 0)beginif @columntype in (39,47) /* char fields need quotes (except when entering NULL);* use char(39) == ', easier readable than escaping*/beginselect @rightpart = @rightpart + '+'select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'endelse if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes *//* convert to VC 1000 to leave space for other fields */beginselect @rightpart = @rightpart + '+'select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),' + @columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'endelse if @columntype in (58,61,111) /* datetime fields */beginselect @rightpart = @rightpart + '+'select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),' + @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'endelse /* numeric types */beginselect @rightpart = @rightpart + '+'select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'endif ( @columncount < @columncount_max)beginselect @rightpart = @rightpart + '+'','''endendselect @columncount = @columncount + 1endendselect @rightpart = @rightpart + '+'')''' + ' from ' + @tablename-- Order the select-statements by the first column so you have the same order for-- different database (easy for comparisons between databases with different creation orders)select @rightpart = @rightpart + ' order by 1'-- For tables which contain an identity column we turn identity_insert on-- so we get exactly the same contentif @hasident > 0select 'SET IDENTITY_INSERT ' + @tablename + ' ON'exec ( @leftpart + @rightpart )if @hasident > 0select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'select @tablename = MIN (name)from #tablenameswhere name > @tablenameendend