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 { get; set; } 

应用二，生成列表页面和添加页面：

首先需要给每个字段添加一个扩展属性，其中参数@value赋值备注、@level1name赋值表名、@level2name赋值字段名。

EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'主?键?',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'BingoT1', @level2type = N'COLUMN', @level2name = N'id' 

EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'姓?名?',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'BingoT1', @level2type = N'COLUMN', @level2name = N'NAME' 

EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'身?份?证?号?码?',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'BingoT1', @level2type = N'COLUMN',
    @level2name = N'IdentityNo' 

EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'录?入?日?期?',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
    @level1name = N'BingoT1', @level2type = N'COLUMN',
    @level2name = N'LogDate' 

然后就可以使用系统函数生成列表页面代码.

SELECT  '<asp:BoundColumn DataField="' + objname + '" HeaderText="'
        + CAST(value AS NVARCHAR) + '"' + CASE CAST(value AS NVARCHAR)
                                            WHEN '主?键?' THEN ' Visible="False"'
                                            ELSE ''
                                          END + '></asp:BoundColumn> '
FROM    FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1',
                                'column', DEFAULT) 

结果如下（在查询分析器中以文本格式显示结果，快捷键Ctrl+T）：

    <asp:BoundColumn DataField="id" HeaderText="主键" Visible="False"></asp:BoundColumn>
    <asp:BoundColumn DataField="NAME" HeaderText="姓名"></asp:BoundColumn>
    <asp:BoundColumn DataField="IdentityNo" HeaderText="身份证号"></asp:BoundColumn>
    <asp:BoundColumn DataField="LogDate" HeaderText="录入日期"></asp:BoundColumn>

再生成添加页面代码，其中Char（13）代表回车符.

SELECT '<tr>'+ CHAR(13) 
            +' <td class="add_tdr" style=" 30%;">' + CAST(VALUE AS NVARCHAR) + '：?</td>' + CHAR(13) 
            +' <td class="add_tdl" style=" 70%;">' 
            +'<asp:TextBox ID="txt' + objname + '" runat="server" style="220px"></asp:TextBox> <td>' + CHAR(13) 
        +'</tr> ' 
FROM FN_LISTEXTENDEDPROPERTY(NULL, 'user', 'dbo', 'table', 'BingoT1','column', DEFAULT) 

结果如下（在查询分析器中以文本格式显示结果，快捷键Ctrl+T）：

 <tr>
        <td class="add_tdr" style="width: 30%;">
            主键：
        </td>
        <td class="add_tdl" style="width: 70%;">
            <asp:TextBox ID="txtid" runat="server" Style="width: 220px"></asp:TextBox>
            <td>
    </tr>
    <tr>
        <td class="add_tdr" style="width: 30%;">
            姓名：
        </td>
        <td class="add_tdl" style="width: 70%;">
            <asp:TextBox ID="txtNAME" runat="server" Style="width: 220px"></asp:TextBox>
            <td>
    </tr>
    <tr>
        <td class="add_tdr" style="width: 30%;">
            身份证号码：
        </td>
        <td class="add_tdl" style="width: 70%;">
            <asp:TextBox ID="txtIdentityNo" runat="server" Style="width: 220px"></asp:TextBox>
            <td>
    </tr>
    <tr>
        <td class="add_tdr" style="width: 30%;">
            录入日期：
        </td>
        <td class="add_tdl" style="width: 70%;">
            <asp:TextBox ID="txtLogDate" runat="server" Style="width: 220px"></asp:TextBox>
            <td>
    </tr>

页面效果图如下所示：