mssql server 操作-读取 数据表的 字段名、字段类型、位数、字段说明、外键、检查约束等

mssql server 操作-读取 数据表的 字段名、字段类型、位数、字段说明、外键、检查约束等

1、创建登录名称

Use master;
CREATE LOGIN 你的登录名称 WITH PASSWORD = '你的超强密码';

2、创建用户

Use 你的数据库名称;
Create user 你的用户名称 for login 你的登录名称;
GRANT select,insert,update,delete on SCHEMA::dbo to 你的用户名称; 
GRANT EXECUTE ON SCHEMA::dbo TO 你的用户名称;

3、将 db_owner 架构的所有者权限转移给 dbo 数据库用户

alter  Authorization on SCHEMA::db_owner to dbo;

语法拆解

ALTER AUTHORIZATION    用于更改对象（如表、视图、存储过程、架构等）的所有者。
ON SCHEMA::db_owner    指定要更改所有者的对象是名为 db_owner 的架构（Schema）。
::                     是作用域限定符，用于指定对象类型。
TO dbo                 将所有权转移给 dbo（Database Owner，数据库所有者）。

4、读取 数据表的 字段名、字段类型、位数、字段说明

SELECT 
  c.name AS '字段名', ty.name AS '数据类型', c.max_length AS '长度', c.precision AS '小数位数', 
  c.is_nullable AS '可为空', ep.value AS '字段说明', is_identity as '关键字段' -- 这就是字段的注释
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.extended_properties ep 
    ON ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id 
    AND ep.name = 'MS_Description'  -- 筛选出描述信息
WHERE t.name = 'AuditReport';          -- 替换为实际的表名

5、显示每个字段的所有约束信息

SELECT SCHEMA_NAME(t.schema_id) AS '架构',  t.name AS '表名',    c.name AS '字段名',    c.column_id AS '字段序号',
    c.is_nullable AS '是否可为空',    -- 主键约束    CASE WHEN EXISTS(
        SELECT 1 FROM sys.index_columns ic 
        INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        WHERE i.is_primary_key = 1 AND ic.object_id = t.object_id AND ic.column_id = c.column_id
    ) THEN '是' ELSE '否' END AS '是否主键',
    -- 唯一约束
    CASE WHEN EXISTS(
        SELECT 1 FROM sys.index_columns ic 
        INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        WHERE i.is_unique = 1 AND i.is_primary_key = 0 AND ic.object_id = t.object_id AND ic.column_id = c.column_id
    ) THEN '是' ELSE '否' END AS '是否唯一',
    -- 外键约束
    STUFF((SELECT ', ' + fk.name + '->' + OBJECT_NAME(fk.referenced_object_id) + '.' 
                  + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
        FROM sys.foreign_key_columns fkc
        INNER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
        WHERE fkc.parent_object_id = t.object_id AND fkc.parent_column_id = c.column_id
        FOR XML PATH('')
    ), 1, 2, '') AS '外键约束',
    -- 检查约束
    STUFF((
        SELECT ', ' + cc.name + ': ' + cc.definition
        FROM sys.check_constraints cc
        WHERE cc.parent_object_id = t.object_id AND cc.parent_column_id = c.column_id
        FOR XML PATH('')
    ), 1, 2, '') AS '检查约束',
    -- 默认约束
    STUFF((
        SELECT ', ' + dc.name + ': ' + dc.definition
        FROM sys.default_constraints dc
        WHERE dc.parent_object_id = t.object_id AND dc.parent_column_id = c.column_id
        FOR XML PATH('')
    ), 1, 2, '') AS '默认约束'
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'AuditReport'  -- 替换为你的表名
ORDER BY c.column_id;

6、检查约束

ALTER TABLE [dbo].[AuditReport]  WITH CHECK 
ADD CHECK  (([RectificationType]=(3) OR [RectificationType]=(2) OR [RectificationType]=(1) OR [RectificationType] IS NULL))

这条 SQL 语句的作用是：为 AuditReport 表中的 RectificationType 字段添加一个检查约束，

限制该字段只能插入或更新为特定的几个值（1、2、3 或 NULL）。

下面我们来逐段拆解它的含义：

语法拆解

ALTER TABLE [dbo].[AuditReport]  表示要修改 AuditReport 这张表的结构。
WITH CHECK    这是默认行为，通常可以省略。它的作用是：在添加约束时，验证表中已有的数据是否符合这个新约束。

如果表中已经存在 RectificationType 为 4 或 0 等不符合条件的数据，这条语句会执行失败，从而阻止添加约束，避免产生不合规的旧数据。
与之相对的选项是 WITH NOCHECK，它会强制添加约束但不验证旧数据（不推荐，会导致“脏数据”）。

ADD CHECK  表示添加一个检查约束。这个约束会强制对 RectificationType 字段的值进行规则校验。
(([RectificationType]=(3) OR [RectificationType]=(2) OR [RectificationType]=(1) OR [RectificationType] IS NULL))

这是约束的具体规则。它限定了 RectificationType 字段只能取以下值：

1   2  3  或者 NULL（空值）

总结：这条命令的业务含义

结合表名 AuditReport（审计报告）和字段名 RectificationType（整改类型），可以推断出这条命令的目的是：
在数据库层面强制规定“整改类型”只能是 1、2、3 这三种预设类型，或者暂时不填（NULL）。

更推荐的写法（可读性更好）

ALTER TABLE [dbo].[AuditReport]
ADD CONSTRAINT CHK_AuditReport_RectificationType 
CHECK (RectificationType IN (1, 2, 3) OR RectificationType IS NULL);