Professional Web Applications Themes

Sp_rename - Microsoft SQL / MS SQL Server

Hi everybody, I am trying to rename a table dynamically...i tried various way, i was still not succefully. Could anyboyd pl. help me out on this... declare i int declare tablename varchar(50) DECLARE String varchar (1000) set i=2 set tablename ='test1'+cast(i as varchar(1)) select tablename --exec sp_rename 'test1',trim(tablename) set String='sp_rename '''test1''','+trim(tablename) exec sp_executesql string Thanks Jenniffer...

  1. #1

    Default Sp_rename

    Hi everybody,
    I am trying to rename a table dynamically...i tried
    various way, i was still not succefully.

    Could anyboyd pl. help me out on this...


    declare i int
    declare tablename varchar(50)
    DECLARE String varchar (1000)
    set i=2
    set tablename ='test1'+cast(i as varchar(1))
    select tablename
    --exec sp_rename 'test1',trim(tablename)
    set String='sp_rename '''test1''','+trim(tablename)
    exec sp_executesql string

    Thanks
    Jenniffer
    jeniffer Guest

  2. #2

    Default Re: Sp_rename

    You don't need dynamic SQL because sp_rename allows the use of a variable as
    a parameter anyway. There is no TRIM() function in SQL, use LTRIM/RTRIM,
    though in this example it's redundant.

    DECLARE i INTEGER
    DECLARE tablename VARCHAR(50)

    SET i=2
    SET tablename = LTRIM(RTRIM('test1'+CAST(i AS VARCHAR(1))))
    EXEC sp_rename 'test1', tablename

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Sp_rename

    You had a few syntax errors in your code...

    There is no trim function in SQL server... only ltrim and rtrim....

    Try this:


    create table test1
    (id int)

    declare i int
    , tablename nvarchar(50)
    , string nvarchar (1000)

    insert into test1 values(1)
    insert into test1 values(2)

    select id_from_test1 = id from test1

    set i=2
    set tablename = 'test' + cast(i as varchar(1))

    set string = 'sp_rename ''test1'',''' + ltrim(rtrim(tablename)) + ''''
    exec sp_executesql string

    select id_from_test2 = id from test2

    --drop table test1
    drop table test2

    --
    Dean Savovic
    www.teched.hr


    "jeniffer" <com> wrote in message news:08c701c34f98$b165fbe0$gbl... 


    Dean Guest

  4. #4

    Default Re: Sp_rename


    Thanks a lot ...

    thats works
     
    use of a variable as 
    use LTRIM/RTRIM, 
    (1)))) 
    jeniffer Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139