|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to rename a temp tableEXEC sp_rename '#customers', '#custs'
result with error :Invalid object name '#customers'. how to rename a temp table? Sam
sp_rename Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database. Show quote "Sam" <focu***@zahav.net.il> wrote in message news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... > EXEC sp_rename '#customers', '#custs' > result with error :Invalid object name '#customers'. > > how to rename a temp table? > > > > Uri, I believe sp_rename disallows renaming temp objects.
-- Show quoteHope this helps. Dan Guzman SQL Server MVP "Uri Dimant" <u***@iscar.co.il> wrote in message news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... > Sam > sp_rename > Changes the name of a user-created object (for example, table, column, or > user-defined data type) in the current database. > > > > > > > > "Sam" <focu***@zahav.net.il> wrote in message > news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >> EXEC sp_rename '#customers', '#custs' >> result with error :Invalid object name '#customers'. >> >> how to rename a temp table? >> >> >> >> > > Hi, Dan
>> Changes the name of a user-created object (for example, table, column, or That was exactly may point.>> user-defined data type) in the current database. Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:OpFv7%23urFHA.2540@TK2MSFTNGP09.phx.gbl... > Uri, I believe sp_rename disallows renaming temp objects. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... >> Sam >> sp_rename >> Changes the name of a user-created object (for example, table, column, or >> user-defined data type) in the current database. >> >> >> >> >> >> >> >> "Sam" <focu***@zahav.net.il> wrote in message >> news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >>> EXEC sp_rename '#customers', '#custs' >>> result with error :Invalid object name '#customers'. >>> >>> how to rename a temp table? >>> >>> >>> >>> >> >> > > Uri, I didn't get your point from the BOL excerpt. Even if one sets the
database context to tempdb, the temp table can't be renamed even though it is a 'user-created' object. Examining the sp_rename text, I see the following comment: -- Disallow renaming object to or from a temp name (starts with #) -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Uri Dimant" <u***@iscar.co.il> wrote in message news:OMlCMHvrFHA.3440@TK2MSFTNGP10.phx.gbl... > Hi, Dan >>> Changes the name of a user-created object (for example, table, column, >>> or user-defined data type) in the current database. > > That was exactly may point. > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:OpFv7%23urFHA.2540@TK2MSFTNGP09.phx.gbl... >> Uri, I believe sp_rename disallows renaming temp objects. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Uri Dimant" <u***@iscar.co.il> wrote in message >> news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... >>> Sam >>> sp_rename >>> Changes the name of a user-created object (for example, table, column, >>> or user-defined data type) in the current database. >>> >>> >>> >>> >>> >>> >>> >>> "Sam" <focu***@zahav.net.il> wrote in message >>> news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >>>> EXEC sp_rename '#customers', '#custs' >>>> result with error :Invalid object name '#customers'. >>>> >>>> how to rename a temp table? >>>> >>>> >>>> >>>> >>> >>> >> >> > > Dan
>> Changes the name of a user-created object (for example, table, column, or It says table........... and nothing about temporary table. ( At least I >> user-defined data type) in the current database. understand from this description) As I see it is not documented well. > Uri, I didn't get your point from the BOL excerpt. Even if one sets the Yes , does not matter if you are in tempdb you can't rename a temp. table.> database context to tempdb, the temp table can't be renamed even though it > is a 'user-created' object. Examining the sp_rename text, I see the > following comment: Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23KTL%23UvrFHA.2064@TK2MSFTNGP09.phx.gbl... > Uri, I didn't get your point from the BOL excerpt. Even if one sets the > database context to tempdb, the temp table can't be renamed even though it > is a 'user-created' object. Examining the sp_rename text, I see the > following comment: > > -- Disallow renaming object to or from a temp name (starts with #) > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:OMlCMHvrFHA.3440@TK2MSFTNGP10.phx.gbl... >> Hi, Dan >>>> Changes the name of a user-created object (for example, table, column, >>>> or user-defined data type) in the current database. >> >> That was exactly may point. >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:OpFv7%23urFHA.2540@TK2MSFTNGP09.phx.gbl... >>> Uri, I believe sp_rename disallows renaming temp objects. >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "Uri Dimant" <u***@iscar.co.il> wrote in message >>> news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... >>>> Sam >>>> sp_rename >>>> Changes the name of a user-created object (for example, table, column, >>>> or user-defined data type) in the current database. >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> "Sam" <focu***@zahav.net.il> wrote in message >>>> news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >>>>> EXEC sp_rename '#customers', '#custs' >>>>> result with error :Invalid object name '#customers'. >>>>> >>>>> how to rename a temp table? >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > so how can i rename the temp table?
Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... > Sam > sp_rename > Changes the name of a user-created object (for example, table, column, or > user-defined data type) in the current database. > > > > > > > > "Sam" <focu***@zahav.net.il> wrote in message > news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >> EXEC sp_rename '#customers', '#custs' >> result with error :Invalid object name '#customers'. >> >> how to rename a temp table? >> >> >> >> > > select * into #NewName from #OldName
drop table #OldName -- Show quote--Brian (Please reply to the newsgroups only.) "Sam" <focu***@zahav.net.il> wrote in message news:uBSEB%23urFHA.1168@TK2MSFTNGP11.phx.gbl... > so how can i rename the temp table? > > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:OziOz4urFHA.240@tk2msftngp13.phx.gbl... >> Sam >> sp_rename >> Changes the name of a user-created object (for example, table, column, or >> user-defined data type) in the current database. >> >> >> >> >> >> >> >> "Sam" <focu***@zahav.net.il> wrote in message >> news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... >>> EXEC sp_rename '#customers', '#custs' >>> result with error :Invalid object name '#customers'. >>> >>> how to rename a temp table? >>> >>> >>> >>> >> >> > > Sam wrote:
> EXEC sp_rename '#customers', '#custs' I'm not sure you can rename a temporary table. Why do you need to do > result with error :Invalid object name '#customers'. > > how to rename a temp table? this? I don't think there's a documented way to do it.
Why would you ever want to rename a temp table? This seems especially pointless with a local temp table, which after all is intended precisely to give you a locally-scoped name for the table. I'm sure if you explain your requirement we can suggest a better solution to avoid doing this. -- David Portas SQL Server MVP -- sp_rename renames the table in the current database. a temp table is created
in tempdb database and tempdb doesnt have sp_rename stored procedure. the table gets deleted immediately after the session is closed, so why do u want to rename it -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://www.SQLResource.com/ --------------------------------------- "Sam" wrote: > EXEC sp_rename '#customers', '#custs' > result with error :Invalid object name '#customers'. > > how to rename a temp table? > > > > > > how to rename a temp table? What would be the point?Sam,
Why do you want to rename a # table. It just exits in that particular session and got cleared of automatically. So instead of renaming while creating give a name which suits for you :) Thanks Hari SQL Server MVP Show quote "Sam" <focu***@zahav.net.il> wrote in message news:ecE3BxurFHA.2008@TK2MSFTNGP10.phx.gbl... > EXEC sp_rename '#customers', '#custs' > result with error :Invalid object name '#customers'. > > how to rename a temp table? > > > >  |
|||||||||||||||||||||||
Other interesting topics