使用系统表---之批量规范外键命名

设计数据库时，规范命名是一项不可或缺的任务。但是我们现在习惯于企业管理器的鼠标操作，很多时候我们都是用了系统的缺省命名。然而，作为正规的开发我们还是想规范他们的命名，由此我就想到了系统表中存放了所有数据库对象的信息，我们既可以利用Sql 读取他们 也可以根据他们 修改已有数据！

以外键为例，首先我们到数据库中查询一下所有的外键信息吧

/*------------------------------- 查询当前库中所有的外键信息 ------------------------------*/

select b.name as fkName, c.tName as fTableName, c.colName, d.tName as rTableName, d.colName
from SysForeignkeys as a
 join SysObjects as b on a.constid = b.id
 join (
  select a.id as tID, a.name as tName, b.colID, b.name as colName
  from SysObjects as a
   join SysColumns as b on a.id = b.id
 ) as c on a.fkeyid = c.tID and a.fkey = c.colID
 join (
  select a.id as tID, a.name as tName, b.colID, b.name as colName
  from SysObjects as a
   join SysColumns as b on a.id = b.id
 ) as d on a.rkeyid = d.tID and a.rkey = d.colID

go

 既然我们能将当前外键信息查出，那么我们只要对查询结果稍加处理就可以实现我们的想法了！

以下是我的具体实现代码，外键命名规范为 fk 开头然后是 关联表表名、外键字段名、被关联表表名、被关联表主键字段名称，中间以 两个下划线（ “__”）连接。

/*------------------------- 生成更改当前库外键名称的Sql -----------------------------*/

select 'sp_rename '
 + nchar(39) + b.name + nchar(39)
 + ', '
 + nchar(39) + 'fk__'
 + c.tName + '__'
 + c.colName + '__'
 + d.tName + '__'
 + d.colName + nchar(39) as sql,
 identity(int, 1, 2) as id into #t1
from SysForeignkeys as a
 join SysObjects as b on a.constid = b.id
 join (
  select a.id as tID, a.name as tName, b.colID, b.name as colName
  from SysObjects as a
   join SysColumns as b on a.id = b.id
 ) as c on a.fkeyid = c.tID and a.fkey = c.colID
 join (
  select a.id as tID, a.name as tName, b.colID, b.name as colName
  from SysObjects as a
   join SysColumns as b on a.id = b.id
 ) as d on a.rkeyid = d.tID and a.rkey = d.colID

go

select 'go' as sql, identity(int, 2, 2) as id into #t2
from #t1
go

select sql
from (
 select * from #t1
 union
 select * from #t2
) as t
order by id
go

drop table #t1
go
drop table #t2
go

将查询的结果 再执行一次就会实现我们想看到的结果！