create proc SPGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
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 '+''''''''' + ' + 'convert(varchar,'+ name +',20)'+ '+'''''''''+' 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 ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') '' as CC, '' ' + left(@sqlValues,len(@sqlValues)-4) + ')'' as VV from '+@tablename
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
print @sql
exec (@sql)
end
调用生成文件:
exec master..xp_cmdshell 'bcp "exec ktcellsmweb_sjz..SPGenInsertSQL ''tb_rolePurview'' " queryout E:/需求工单/数据库脚本/db/web/基本数据脚本/tb_rolePurview.sql -c -S(local) -Usa -Pjnkt'
对于有56类型的:
生成脚本执行时,需要在脚本开头和结尾加上:
SET IDENTITY_INSERT 表名 ON
SET IDENTITY_INSERT 表名 OFF
基于SQL的sysColumns 数据库字段表
当前数据库的所有字段都保留在里面。
重要字段解释:
sysColumns (
name sysname, --字段名称
id int, --该字段所属的表的ID
xtype tinyInt, --该字段类型,关联sysTypes表
length smallint, --该字段物理存储长度
...
)
比如要查询某一个张表的有那些字段和这些字段的长度