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;
    }
}

Mohammad Dehghan · Accepted Answer · 2012-03-20T13:11:41.440

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 '12 at 13:11

answered Mar 17 '12 at 10:35

Mohammad Dehghan

17,853
3
55
72

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? – ahmd0 Mar 17 '12 at 20:10
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! – Mohammad Dehghan Mar 18 '12 at 06:55
Thanks for correcting it. So having said about [ and ], this makes your C# above incorrect -- it will simply remove all instances of [ or ], right? – ahmd0 Mar 20 '12 at 02:17
Actually, right :) But using these characters ([,]) in identifier names are very rare, and it is better to be avoided. – Mohammad Dehghan Mar 20 '12 at 12:59
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? – ahmd0 Mar 20 '12 at 22:27
I added C# code to my original post to remove brackets. I'm not really good with regexp to find a two-line solution... – ahmd0 Mar 20 '12 at 23:00
@ahmd0 As I said in the post, there is no need to remove brackets. Why do you want to do this? For the second parameter of the `sp_rename`? – Mohammad Dehghan Mar 21 '12 at 07:05
I need it for both parameters. – ahmd0 Mar 22 '12 at 03:17
`strign.Replace` is the most simple way. `string cmd = string.format( "EXEC sp_rename '{0}', '{1}'", oldName, newName.Replace("[", "").Replace("]",""))`. But the question is, how your new name could contain brackets? – Mohammad Dehghan Mar 22 '12 at 14:03

score 1 · Answer 2 · edited May 23 '17 at 12:27

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 '17 at 12:27

Community

1
1

answered Mar 17 '12 at 10:29

Jason Evans

28,906
14
90
154

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

2 Answers2