sqlserver建表及注释
时间：2021-07-01 10:21:17
帮助过：3人阅读
**********************************************创建表********************************************************************************************************************
create table sys_user(
id int identity(
1,
1)
primary key not null,
--id主键 自增长
loginName
nvarchar(
50)
NOT NULL UNIQUE,
--登录用户名
password
nvarchar(
50)
NOT NULL ,
--登录密码
email
varchar(
255)
NOT NULL UNIQUE,
--用户邮箱
createTime
datetime default CONVERT(
varchar(
100),
GETDATE(),
20),
--用户创建时间
code
varchar(
255) ,
--‘激活码‘
state
int NOT NULL default 0 ,
--用户激活状态：0表示未激活，1表示激活
nstatusid
int default 1 --用户状态 0无效 1有效
);
--**********************************************给表和列添加注释***********************************************************************************************************
-- 添加表注释 execute sp_addextendedproperty ‘MS_Description‘,‘注释内容‘, ‘user‘,‘dbo‘,‘table‘,‘表名称‘,null,null;
-- 修改表注释 execute sp_updateextendedproperty ‘MS_Description‘,‘注释内容1‘, ‘user‘,‘dbo‘,‘table‘,‘表名称‘,null,null;
-- 删除表注释 execute sp_dropextendedproperty ‘MS_Description‘, ‘user‘,‘dbo‘,‘table‘,‘表名称‘,null,null;
--字段添加注释 execute sp_addextendedproperty ‘MS_Description‘,‘注释内容‘, ‘user‘,‘dbo‘,‘table‘,‘表名‘,‘column‘,‘字段名‘;
--修改字段注释 execute sp_updateextendedproperty ‘MS_Description‘,‘注释内容1‘, ‘user‘,‘dbo‘,‘table‘,‘表名‘,‘column‘,‘字段名‘;
--删除字段注释 execute sp_dropextendedproperty ‘MS_Description‘, ‘user‘,‘dbo‘,‘table‘,‘表名‘,‘column‘,‘字段名‘;
--<!--下面的写法和上面的注释的是一个意思-->
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘用户登录表‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘id主键 自增长‘, N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘id‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘登录用户名‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘loginName‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘登录密码‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘password‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘用户邮箱‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘email‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘用户创建时间‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘createTime‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘激活码‘ , N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘code‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘用户激活状态：0表示未激活，1表示激活‘, N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘state‘;
EXEC sp_addextendedproperty N
‘MS_Description‘, N
‘用户状态 0无效 1有效‘, N
‘SCHEMA‘, N
‘dbo‘,N
‘TABLE‘, N
‘sys_user‘, N
‘COLUMN‘, N
‘nstatusid‘;
--**********************************************查看表简单信息*************************************************************************************************************
SELECT
A.name AS 表名,
B.name AS 表字段,
C.value AS 字段注释
FROM sys.tables A
INNER JOIN sys.columns B
ON B.
object_id = A.
object_id
LEFT JOIN sys.extended_properties C
ON C.major_id
= B.
object_id AND C.minor_id
= B.column_id
WHERE A.name
= ‘sys_user‘;
--表名称
--**********************************************查看表详细信息**************************************************************************************************************
--USE 库名;
SELECT
表名 = Case When A.colorder
=1 Then D.name
Else ‘‘ End,
表说明 = Case When A.colorder
=1 Then isnull(F.value,
‘‘)
Else ‘‘ End,
字段序号 = A.colorder,
字段名 = A.name,
类型 = B.name,
字段说明 = isnull(G.
[value],
‘‘),
标识 = Case When COLUMNPROPERTY( A.id,A.name,
‘IsIdentity‘)
=1 Then ‘√‘Else ‘‘ End,
主键 = Case When exists(
SELECT 1 FROM sysobjects
Where xtype
=‘PK‘ and parent_obj
=A.id
and name
in (
SELECT name
FROM sysindexes
WHERE indid
in(
SELECT indid
FROM sysindexkeys
WHERE id
= A.id
AND colid
=A.colid)))
then ‘√‘ else ‘‘ end,
占用字节数 = A.Length,
长度 = COLUMNPROPERTY(A.id,A.name,
‘PRECISION‘),
小数位数 = isnull(
COLUMNPROPERTY(A.id,A.name,
‘Scale‘),
0),
允许空 = Case When A.isnullable
=1 Then ‘√‘Else ‘‘ End,
默认值 = isnull(E.
Text,
‘‘)
FROM
syscolumns A Left Join systypes B
On A.xusertype
=B.xusertype
Inner Join sysobjects D
On A.id
=D.id
and D.xtype
=‘U‘ and D.name
<>‘dtproperties‘
Left Join syscomments E
On A.cdefault
=E.id
Left Join sys.extended_properties G
On A.id
=G.major_id
and A.colid
=G.minor_id
Left Join sys.extended_properties F
On D.id
=F.major_id
and F.minor_id
=0
where d.name
=‘sys_user‘ --如果只查询指定表,加上此条件
Order By
A.id,A.colorder;
sqlserver建表及注释
标签：har schema desc 默认 isnull comment ext code 查询
