Sql server 实用技巧之主键、系统表与代码生成器(一)

一、 从主键说起

CREATE TABLE BingoT1

(

id INT IDENTITY(1, 1) ,

NAME NVARCHAR(10) , --姓 名

IdentityNo VARCHAR(18) , --身 份 证 号 码

LogDate DATETIME DEFAULT ( GETDATE() ) , --录 入 日 期

PRIMARY KEY ( id )--主 键 约 束

)

Go

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '李 斌 ', '123456789012345' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '张 霖 青 ', '123456789012345678' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '李 菲 ', '12345678901234500x' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '戈 晓 娟 ', '123456789012345' )

Go

SELECT * FROM BingoT1

我们创建示例表BingoT1 并插入若干条数据，主键是一个自增字段。

问题一：误删了一条记录如何修复？
在企业管理器（sql 2000）或Sql Server Management Studio中无法修改Id字段，如图：

解决办法：

SET IDENTITY_INSERT bingot1 ON

INSERT INTO BingoT1(id, NAME, identityNo ) VALUES ( 1, '李 斌 ', '123456789012345' )

SET IDENTITY_INSERT bingot1 OFF --用 完 一 定 要 关 闭 执行后查看数据如下：

从LogDate的时间上可以看出Id=1的数据是最后插入的。

问题二：Delete掉所有的数据后，为什么Id不从1 开始？

DELETE FROM bingot1

Go

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '李 斌 ', '123456789012345' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '张 霖 青 ', '123456789012345678' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '李 菲 ', '12345678901234500x' )

INSERT INTO BingoT1( NAME, identityNo )

VALUES ( '戈 晓 娟 ', '123456789012345' )

Go

SELECT * FROM BingoT1

Go执行后查看数据如下：

解决办法：

TRUNCATE TABLE BingoT1

GO

如果表包含标识列，该列的计数器重置为该列定义的种子值。如果未定义种子，则使用默认值1。若要保留标识计数器，请使用DELETE。

TRUNCATE TABLE删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 在功能上与没有WHERE 子句的DELETE 语句相同；但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少。

二、SQL SERVER 的中枢神经--系统表

SysObjects

存储数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）。

SELECT * FROM sysobjects WHERE xtype='U'

SELECT * FROM SysObjects WHERE xtype='D'

SELECT * FROM SysObjects WHERE xtype='PK'

执行后可以看到我们新创建的表BingoT1、表的默认约束DF__BingoT1__LogDate__07020F21（以DF开头）、表的主键PK__BingoT1__060DEAE8（以PK开头）。

Xtype标识对象类型。可以是下列对象类型中的一种：

C = CHECK 约束D = 默认设置或DEFAULT 约束

F = FOREIGN KEY 约束L = 日志FN = 标量函数IF = 内联表函数

P = 存储过程PK = PRIMARY KEY 约束（类型为K）RF = 复制筛选器存储过程

S = 系统表TF = 表函数TR = 触发器U = 用户表UQ = UNIQUE 约束（类型为K）

V = 视图X = 扩展存储过程

可以通过下面的语句查看和表相关的各种资源：

--查看和表相关的各种资源

SELECT *

FROM sysobjects

WHERE parent_obj IN ( SELECT id

FROM sysobjects

WHERE name = 'BingoT1' )

SysColumns

存储表和视图中的列，并保存数据库中的存储过程的每个参数。可以使用如下语句查看表BINGOT1的所有列信息。

--查看表的所有列信息

SELECT *

FROM SysColumns

WHERE id IN ( SELECT id

FROM sysobjects

WHERE name = 'BingoT1' )

Sys.types

存放物理存储类型。内容如下所示：

SELECT * FROM sys.types 

问题：系统表我很少用，有什么实际应用吗？
下面我们就用这3个系统表来写一个简单的代码生成器！
应用一，生成实体类属性：

SELECT  'public virtual ' + CASE t.name

WHEN 'int' THEN 'int'

ELSE 'string'

END + ' ' + c.name + ' { get; set; }'

FROM dbo.sysobjects AS o

INNER JOIN dbo.syscolumns AS c ON c.id = o.id

INNER JOIN dbo.systypes AS t ON c.xusertype = t.xusertype

WHERE o.type = 'U'

AND o.name = 'BingoT1'

输出结果如下：

public virtual int id { get; set; }

public virtual string NAME { get; set; }

public virtual string IdentityNo { get; set; }

public virtual string LogDate { ge