புள்ளிவலை

   1: USE  தகவல்தளம் ;

   2:  

   3: declare @tablename varchar(255)

   4: set @tablename = 'test'

   5:  

   6: declare @proceed varchar(20)

   7:  

   8:  

   9: select @proceed = object_id(@tablename)

  10:  

  11: select @proceed = [type] from sys.objects where name = @tablename

  12:  

  13: if @proceed is null

  14: BEGIN

  15:     print 'Table "' + @tablename + '" does not exist in this database. '

  16:     + char(13) + 'Please try changing the database name in the USE statement or

  17:     try correcting the table name.'

  18:     return

  19: END

  20:  

  21: if not @proceed = 'U'

  22: BEGIN

  23:     print 'Object "' + @tablename + '" is not a table...'

  24:     return

  25: END

  26:  

  27: declare @structure table

  28: (

  29: colname varchar(1000),

  30: coltype varchar(50),

  31: colsize int

  32: )

  33:  

  34: insert into @structure

  35: select sys.columns.name, sys.types.name, sys.columns.max_length

  36: from sys.columns left join sys.types

  37: on sys.columns.system_type_id = sys.types.system_type_id

  38: where object_id = object_id(@tablename)

  39:  

  40:  

  41: declare @declaration varchar(8000)

  42: set @declaration = ''

  43: select @declaration = @declaration + char(13) + '@' + colname

  44: + ' ' + coltype + '(' +

  45: convert(varchar(20) , colsize) + '),'

  46: from @structure

  47:  

  48: --Build Insert statement

  49: declare @statement varchar(8000)

  50:  

  51: set @statement = ''

  52:  

  53: select @statement = @statement + char(13)

  54:  

  55: SELECT @STATEMENT = @statement + 'INSERT INTO ' + @tablename + ' ('

  56:  

  57: SELECT @STATEMENT = @statement + char(13) + colname + ',' from @structure

  58:  

  59: SELECT @STATEMENT = @STATEMENT + ')'

  60:  

  61: select @statement = replace(@statement, ',)', ')')

  62:  

  63: select @statement = @statement + ' values('

  64:  

  65:  

  66:  

  67: SELECT @STATEMENT = @STATEMENT + char(13) + '@' + colname + ',' from @structure

  68:  

  69: select @statement = @statement + ')'

  70:  

  71: select @statement = replace(@statement, ',)', ')')

  72:  

  73:  

  74: select @declaration = substring(@declaration, 1, len(@declaration)-1)

  75:  

  76: print 'CREATE PROCEDURE dbo.PROC1 ' + char(13) + '(' + char(13) +  @declaration

  77: + char(13) + ')' + char(13) + 'AS' + char(13)

  78:  

  79: print char(13)

  80: print @statement

  81:  

  82: --Update

  83: declare @updatestatement varchar(3000)

  84:  

  85: set @updatestatement = 'UPDATE ' + @tablename + ' SET '

  86:  

  87: select @updatestatement  = @updatestatement  + char(13) + ' ' + colname +

  88:     ' = @' + colname + ',' from @structure

  89:  

  90: select @updatestatement = @updatestatement + ')'

  91:  

  92: select @updatestatement = replace(@updatestatement, ',)', char(13) + ' WHERE 1=2')

  93:  

  94: print char(13)

  95:  

  96: print @updatestatement