Create PROCEDURE dbo. UspOutputData @ 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 -- Judge ?? ο? Nbsp; Begin Print 'the object not exists' Return End Set @ objectname = object_name (@ objectId) If @ objectname is null or charindex (@ objectname, @ tablename) = 0 -- this judgment is not strict Begin Print 'object not in current database' Return End If OBJECTPROPERTY (@ objectId, 'istable') <> 1 -- Judgement ?? ο? Able Begin Print 'the object is not table' Return End Select @ ident = status & 0x80 from syscolumns where id = @ objectid and status & 0x80 = 0x80 If @ ident is not null Print 'set IDENTITY_Insert '+ @ TableName + 'on' Declare syscolumns_cursor cursor For select c. name, c. xtype from syscolumns c Where c. id = @ objectid 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 does not need to be processed. image, text, ntext, SQL _variant will not be processed for the moment. Begin Set @ column = @ column + 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 ''' + '+ @ name +' + ''' -- varchar, char When @ xtype in (231,239) then ''' + '+ @ name +' + ''' -- 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 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' + @ tablename + '(' + @ column + ') values ('as ''--'', '+ @ columndata +', '') 'from' + @ tablename Print '--' + @ SQL Exec (@ SQL) If @ ident is not null Print 'set IDENTITY_Insert '+ @ TableName + 'off' GO The execution method is exec UspOutputData, your table name. The statement for data export is obtained. However, the image, text, ntext, and SQL _variant columns cannot appear in the statement, which will be improved later.
|