MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang · 发表于 2012-8-16 22:09

本帖最后由 Cherish_j_wang 于 2012-8-16 22:11 编辑

MSSQL DBA 常用脚本总结

之处理对象名称中特殊字符

场景

前两天被同事问到一个问题：如果我的索引名称中不小心使用了特殊字符（我们暂且叫不规则命名方式），比如：[]，那么如何将这个特殊字符去掉，变成“规则”的命名方式呢？

脚本

测试脚本如下：

use test
go
if OBJECT_ID('dbo.tb_test_naming_objects','U') is not null
drop table dbo.tb_test_naming_objects
GO
create table dbo.tb_test_naming_objects
(
id int identity(1,1) not null
,rowid uniqueidentifier not null
,constraint PK_tb_test_naming_objects primary key
(
id
)
);
create index ix_rowid
ON dbo.tb_test_naming_objects(rowid);
GO
--change index name to unusual object naming
exec sys.sp_rename 'dbo.tb_test_naming_objects.ix_rowid','[ix_tb_test_naming_objects_rowid]','index'
;
--double checking index name
select
OBJECT_NAME = OBJECT_NAME(object_id)
,index_name = name
from sys.indexes
where object_id = object_id('dbo.tb_test_naming_objects','U');
--how to change index name from [ix_tb_test_naming_objects_rowid](naming unusually) to ix_tb_test_naming_objects_rowid(naming usually)
exec sys.sp_rename 'dbo.tb_test_naming_objects.[[ix_tb_test_naming_objects_rowid]]]','ix_tb_test_naming_objects_rowid','index';
--double checking index name again
select
OBJECT_NAME = OBJECT_NAME(object_id)
,index_name = name
from sys.indexes
where object_id = object_id('dbo.tb_test_naming_objects','U');