How to write correct syntax to rename SQL Server database table from C# code?

Question

I'm coding in C# in ASP.NET environment and I need to write a function that takes SQL Server database table and gives it another name.

So from SQL standpoint I need to do this:

EXEC sp_rename 'OldTableName', 'NewTableName';

But the problem is that at times the (old) table name supplied to my function can be something like this: [dbo].[OldTableName] and as far as I can understnad the brackets ('[' and ']') are not the part of the name itself, as well as the "dbo" part.

So how to handle such situation?

EDIT: I was able to come up with C# code to remove brackets (needs to be checked though):

for (int i = 0; i < strTableName.Length; i++)
{
    if (strTableName[i] == '[' ||
    strTableName[i] == ']')
    {
        int j = i;
        for (; j < strTableName.Length && strTableName[j] == strTableName[i]; j++) ;

        int nRepeatCnt = j - i;
        int nNumKeep = nRepeatCnt / 2;
        int nNumRemove = nRepeatCnt - nNumKeep;

        strTableName = strTableName.Remove(i, nNumRemove);
        i += nNumKeep - 1;
    }
}

Moha Dehghan · Accepted Answer · 2012-03-20 13:11:41Z

3

When using SQL Server internal (system) stored procedures and functions (sp_rename, sp_help, OBJECT_ID, ...), there is no need to remove or add delimiters and qualifiers ('[' and ']' or default schema name such as 'dbo'), because these functions parse the identifier names and infer the actual name. Also there are some situations that you require to use the delimiters (When they are not Regular Identifiers. See Identifiers).

For example when renaming dbo.MyTable to dbo.NewTable, all of these command are valid:

sp_rename 'dbo.MyTable', 'NewTable'
sp_rename '[dbo].MyTable', 'NewTable'
sp_rename 'MyTable', 'NewTable'
sp_rename '[dbo].[MyTable]', 'NewTable'

But be noticed that the new name you specify as the second parameter of the sp_rename will not be parsed, and the stored procedure will set the object name exactly as what you specified:

sp_rename 'dbo.MyTable', '[dbo].NewTable'

This changes MyTable to [dbo].NewTable, and your qualified table name is exactly dbo.[dbo].NewTable! Accessing this new table with this name, is a little tricky:

sp_rename 'dbo."[dbo].NewTable"', 'OldTableName'

But when accessing object names in SQL Server system tables (like sys.table, sys.columns, ...), you should not use delimiters and qualifiers, because the identifiers in those table are stored as character strings:

select * from sys.columns where object_id = OBJECT_ID('dbo.Orders') and [name]='OrderID'

OBJECT_ID() is a system function and parses the object name, but OrderID should be specified as the exact column name (case insesitive).

edited Mar 20, 2012 at 13:11

answered Mar 17, 2012 at 10:35

Moha Dehghan

18.5k3 gold badges60 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ahmd0 Over a year ago

Very nice. Thank you. One question, though. Is it safe to assume with your approach that [ or ] cannot be a part of the name itself?

Moha Dehghan Over a year ago

No. They can be a part the name. But these names should be delimited. You can name your table My Table], but when accessing it, you should use [My Table]]]. [ and ] around the name are the delimiters, and of the closing brackets are used to escape the second closing bracket!

ahmd0 Over a year ago

Thanks for correcting it. So having said about [ and ], this makes your C# above incorrect -- it will simply remove all instances of [ or ], right?

Moha Dehghan Over a year ago

Actually, right :) But using these characters ([,]) in identifier names are very rare, and it is better to be avoided.

ahmd0 Over a year ago

It'd be very helpful if we could come up with some C# code to remove them (the right way.) I was trying to think of some regexp, or something. Any ideas?

|

Community · Accepted Answer · 2017-05-23 12:27:47Z

1

The fact that you include [dbo] in renaming the table should not matter. For example:

Why do table names in SQL Server start with "dbo"?

Whether you include [dbo] or not, the rename command should still work.

Is this the case for you?

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Mar 17, 2012 at 10:29

Jason Evans

29.2k16 gold badges95 silver badges157 bronze badges

Collectives™ on Stack Overflow

How to write correct syntax to rename SQL Server database table from C# code?

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related