生成表结构

2005-08-02 T-SQL Store Procedure 修订为:
完全不受字段数量或字段值影响 而生成正确完整的 INSERT INTO ... SQL
ALTER     procedure Z_SP_GenInsertSQL
(
    @tablename varchar(256)
    ,@WhereClause    varchar(1000) = 'where 1 = 1'
)
as
begin
/*
usage:
Z_SP_GenInsertSQL '表名','where '
*/
    declare @sql varchar(8000)
    declare @sqlValues varchar(8000)
    set @sql =' ''(''' + char(13) + ','
    set @sqlValues = ' values ('''+ char(13) + ','
    select @sqlValues = @sqlValues + cols + ' + '',' + '''' + char(13) + ','
            ,@sql = @sql + '''[' + name + '],''' + char(13) + ','
    from
    (
        select
            case
                when xtype in (48,52,56,59,60,62,104,106,108,122,127)
                    then 'case when '+ name + ' is null then ''NULL'' else ' + 'cast(' + name + ' as varchar)' + ' end'
                when xtype in (58,61)
                    then 'case when '+ name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'cast(' + name + ' as varchar)' + '+''''''''' + ' end'
                when xtype in (167,175)
                    then 'case when '+ name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
                when xtype in (231,239)
                    then 'case when '+ name + ' is null then ''NULL'' else ' + '''N'''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
                else '''NULL'''
            end as Cols
            ,name
        from syscolumns
        where id = object_id(@tablename)
                --and autoval is null --忽略自增整型字段
    ) T
    set @sql = 'select ' + char(13) + '''INSERT INTO '''+ char(13) + ','
                 + '''['+ @tablename + ']''' + char(13) + ','
                 + left(@sql,len(@sql)-4) + '''' + char(13) + ','')' + left(@sqlValues,len(@sqlValues)-7) + ','')'''
                 + char(13) + 'from [' + @tablename + ']'
                 + char(13) + @WhereClause
    --select @sql -- select SQL 被截断
    print @sql -- print SQL 是完整正确的
    exec (@sql)
/*
select *
from syscolumns    
where id = object_id('test') and autoval is null
*/
end

不过在我的数据库中有几个表未能生成成功，经分析原来数据库某些列有西文组合字符，被截断了，遂将两变量声明该为： 
declare @sql nvarchar(MAX) 
declare @sqlValues nvarchar(MAX) 
问题解决。