SQL server--script that generates table data through stored procedures

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--Determines whether an object exists

Begin

print ' The object Not EXISTS '

Return

End

Set @objectname =rtrim (object_name (@objectId))

If @objectname is null or CHARINDEX (@objectname, @tablename) =0--This judgment is not tight

Begin

Print ' object not in current database '

Return

End

　　

If OBJECTPROPERTY (@objectId, ' istable ') < > 1--Determine if the object is a table

Begin

print ' The object is not table '

Return

End

　　

Select @ident =status&0x80 from syscolumns where [email protected] and status&0x80=0x80

　　

If @ident is not null

print ' SET identity_insert ' [email protected]+ ' on '

　　

DECLARE syscolumns_cursor cursor

　　

For select C.name,c.xtype from syscolumns c where [e-mail protected] ORDER by C.colid

　　

Open Syscolumns_cursor

Set @column = ' '

Set @columndata = ' '

FETCH NEXT from Syscolumns_cursor to @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 temporarily does not process

Begin

Set @[email protected]+case when Len (@column) =0 then ' Else ', ' [email protected]

Set @[email protected]+case when Len (@columndata) =0 then ' Else ', ' ', ' ', '

End

+case when @xtype into (167,175) then "" "" + "[email protected]+ ' + '" "" "--varchar,char

When @xtype in (231,239) then "N" "+" [email protected]+ ' + ' "" "--nvarchar,nchar

When @xtype =61 and then "" "+convert (char), ' [email protected]+ ', 121] + '" ""--datetime

When @xtype =58 and then "" +convert (char), ' [email protected]+ ', + + '] + ' "'--smalldatetime

When @xtype =36 and then "" "+convert (char), ' [email protected]+ ') + '" "'--uniqueidentifier

else @name End

End

End

FETCH NEXT from Syscolumns_cursor to @name, @xtype

End

Close Syscolumns_cursor

Deallocate syscolumns_cursor

Set @sql = ' Set NOCOUNT on select ' ' Insert ' [email protected]+ ' (' [email protected]+ ') VALUES (' as '--', ' [email protected] + ', ') ' from ' [email protected]

print '--' [email protected]

EXEC (@sql)

If @ident is not null

print ' SET identity_insert ' [email protected]+ ' OFF '

GO

EXEC uspoutputdata your table name

SQL server--script that generates table data through stored procedures