1![]()
CREATE PROCEDURE dbo.UspOutputData
2![]()
@tablename sysname
3![]()
AS
4![]()
declare @column varchar(1000)
5![]()
declare @columndata varchar(1000)
6![]()
declare @sql varchar(4000)
7![]()
declare @xtype tinyint
8![]()
declare @name sysname
9![]()
declare @objectId int
10![]()
declare @objectname sysname
11![]()
declare @ident int
12![]()
13![]()
set nocount on
14![]()
set @objectId=object_id(@tablename)
15![]()
16![]()
if @objectId is null -- 判斷對象是否存在
17![]()
begin
18![]()
print 'The object not exists'
19![]()
return
20![]()
end
21![]()
set @objectname=rtrim(object_name(@objectId))
22![]()
23![]()
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
24![]()
begin
25![]()
print 'object not in current database'
26![]()
return
27![]()
end
28![]()
29![]()
if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table
30![]()
begin
31![]()
print 'The object is not table'
32![]()
return
33![]()
end
34![]()
35![]()
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
36![]()
37![]()
if @ident is not null
38![]()
print 'SET IDENTITY_INSERT '+@TableName+' ON'
39![]()
40![]()
declare syscolumns_cursor cursor
41![]()
42![]()
for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
43![]()
44![]()
open syscolumns_cursor
45![]()
set @column=''
46![]()
set @columndata=''
47![]()
fetch next from syscolumns_cursor into @name,@xtype
48![]()
49![]()
while @@fetch_status < >-1
50![]()
begin
51![]()
if @@fetch_status < >-2
52![]()
begin
53![]()
if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
54![]()
55![]()
begin
56![]()
set @column=@column+case when len(@column)=0 then'' else ','end+@name
57![]()
58![]()
set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
59![]()
end
60![]()
61![]()
+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
62![]()
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
63![]()
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
64![]()
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
65![]()
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
66![]()
else @name end
67![]()
68![]()
end
69![]()
70![]()
end
71![]()
72![]()
fetch next from syscolumns_cursor into @name,@xtype
73![]()
74![]()
end
75![]()
76![]()
close syscolumns_cursor
77![]()
deallocate syscolumns_cursor
78![]()
79![]()
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
80![]()
81![]()
print '--'+@sql
82![]()
exec(@sql)
83![]()
84![]()
if @ident is not null
85![]()
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
86![]()
87![]()
GO
88![]()
89![]()
exec UspOutputData 你的表名
90![]()
CREATE PROCEDURE dbo.UspOutputData 2
@tablename sysname 3
AS 4
declare @column varchar(1000) 5
declare @columndata varchar(1000) 6
declare @sql varchar(4000) 7
declare @xtype tinyint 8
declare @name sysname 9
declare @objectId int 10
declare @objectname sysname 11
declare @ident int 12
13
set nocount on 14
set @objectId=object_id(@tablename) 15
16
if @objectId is null -- 判斷對象是否存在 17
begin 18
print 'The object not exists' 19
return 20
end 21
set @objectname=rtrim(object_name(@objectId)) 22
23
if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 24
begin 25
print 'object not in current database' 26
return 27
end 28
29
if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table 30
begin 31
print 'The object is not table' 32
return 33
end 34
35
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 36
37
if @ident is not null 38
print 'SET IDENTITY_INSERT '+@TableName+' ON' 39
40
declare syscolumns_cursor cursor41
42
for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid 43
44
open syscolumns_cursor 45
set @column='' 46
set @columndata='' 47
fetch next from syscolumns_cursor into @name,@xtype 48
49
while @@fetch_status < >-1 50
begin 51
if @@fetch_status < >-2 52
begin 53
if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 54
55
begin 56
set @column=@column+case when len(@column)=0 then'' else ','end+@name 57
58
set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','59
end 60
61
+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char 62
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar 63
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime 64
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime 65
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 66
else @name end 67
68
end 69
70
end 71
72
fetch next from syscolumns_cursor into @name,@xtype 73
74
end 75
76
close syscolumns_cursor 77
deallocate syscolumns_cursor 78
79
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename 80
81
print '--'+@sql 82
exec(@sql) 83
84
if @ident is not null 85
print 'SET IDENTITY_INSERT '+@TableName+' OFF' 86
87
GO88
89
exec UspOutputData 你的表名90
浙公网安备 33010602011771号