How to rename table name which contain dot character using sp_rename

Question

0.00/5 (No votes)

See more:

Hi I try to rename table name which contain "." using sp_rename in sql server,

E.g:

JavaScript

sp_rename N'1043_MMM.LKJ', N'1043_MMMLKJ'

i will get error like this

C#

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 374
No item by the name of '[1043_MMM.LKJ]' could be found in the current database 'master', given that @itemtype was input as '(null)'.

How to solve this ?

I cant Drop and create table again,bcz it contains value,so i want rename only.
Any special character got restriction for creating table in sql server ? if got pls give information.

Manually can rename table

Regards
Aravind

What I have tried:

if i run in master database in sql server also same error.

Posted 8-Nov-16 17:54pm

Aravindba

Updated 3-Feb-21 4:09am

Add a Solution

Comments

Richard Deeming 20-Dec-16 10:31am

Why are you creating user tables in the master database?

And if it's not in the master database, why are you running your sp_rename script from the wrong database?

Aravindba 21-Dec-16 1:37am

Thank u ur reply,Actually above script working without dot in table name,i am passing that script in command text and that command text connection is local database,not master table.if i run in sql directly without dot it working.

2 solutions

Solution 1

If your table name contains identifier, and if you want to rename it, you should run wrap the tablename with double quotes (“) and use the sp_rename command.

Try this:

sp_rename N'"1043_MMM.LKJ"', N'1043_MMMLKJ'

Posted 8-Nov-16 18:35pm

NiteshAgarwal

Updated 8-Nov-16 18:36pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KenPowers · Accepted Answer · 2021-02-03T04:09:00

Solution 3

You can also place brackets around the string, as shown here:

SQL

EXEC sp_rename N'[1043_MMM.LKJ]', N'1043_MMMLKJ'

Posted 3-Feb-21 4:09am

KenPowers