2023年7月13日发(作者：)

mssql将⼀张表的数据导出sql⽂件CREATE PROCEDURE putData

@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 intset nocount on

set @objectId=object_id(@tablename)if @objectId is null -- 判断对象是否存在

begin

print 'The object not exists'

return

end

set @objectname=rtrim(object_name(@objectId))if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密

begin

print 'object not in current database'

return

endif OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table

begin

print 'The object is not table'

return

endselect @ident=status&0x80 from syscolumns where and status&0x80=0x80if @ident is not null

print 'SET IDENTITY_INSERT ON'declare syscolumns_cursor cursorfor select , from syscolumns c where order by pen syscolumns_cursor

set @column=''

set @columndata=''

fetch next from syscolumns_cursor into @name,@xtypewhile @@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 @column=@column+case when len(@column)=0 then'' else ','end+@nameset @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','

end+case when @xtype in(167,175) then --varchar,char

when @xtype in(231,239) then --nvarchar,nchar

when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime

when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime

when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier

else @name endendendfetch next from syscolumns_cursor into @name,@xtypeendclose syscolumns_cursor

deallocate syscolumns_cursorset @sql='set nocount on select ''insert ) values(''as ''--'','+@columndata+','')'' fromprint

exec(@sql)if @ident is not null

print 'SET IDENTITY_INSERT OFF'

exec putData @tablename = Flm_Info -- sysname