sql系统表学习笔记

最新推荐文章于 2024-05-17 07:15:00 发布

laolaowhn

最新推荐文章于 2024-05-17 07:15:00 发布

阅读量2.6k

点赞数

分类专栏： 1.sql 文章标签： sql null table schema collation object

本文链接：https://blog.csdn.net/laolaowhn/article/details/4617683

版权

1.sql 专栏收录该内容

50 篇文章 0 订阅

订阅专栏

1.首先见两个表test1，test2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_test2_test1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[test2] DROP CONSTRAINT FK_test2_test1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[test1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[test2] GO CREATE TABLE [dbo].[test1] ( [ID] [int] NOT NULL , [Name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [bigint] [bigint] NULL , [binary] [binary] (50) NULL , [bit] [bit] NULL , [char] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [datetime] [datetime] NULL , [decimal] [decimal](18, 0) NULL , [float] [float] NULL , [image] [image] NULL , [int] [int] NULL , [money] [money] NULL , [nchar] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL , [ntext] [ntext] COLLATE Chinese_PRC_CI_AS NULL , [numeric] [numeric](18, 0) NULL , [nvarchar] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [real] [real] NULL , [smalldatetime] [smalldatetime] NULL , [smallint] [smallint] NULL , [smallmoney] [smallmoney] NULL , [sql_variant] [sql_variant] NULL , [text] [text] COLLATE Chinese_PRC_CI_AS NULL , [timestamp] [timestamp] NULL , [tinyint] [tinyint] NULL , [uniqueidentifier] [uniqueidentifier] NULL , [varbinary] [varbinary] (50) NULL , [varchar] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[test2] ( [AutoID] [int] NOT NULL , [ID] [int] NULL , [remark1] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [remark2] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[test1] WITH NOCHECK ADD CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[test2] WITH NOCHECK ADD CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED ( [AutoID] ) ON [PRIMARY] GO CREATE INDEX [IX_test1] ON [dbo].[test1]([Name]) ON [PRIMARY] GO ALTER TABLE [dbo].[test2] ADD CONSTRAINT [FK_test2_test1] FOREIGN KEY ( [ID] ) REFERENCES [dbo].[test1] ( [ID] ) GO exec sp_addextendedproperty N'MS_Description', N'test1表注释', N'user', N'dbo', N'table', N'test1' GO exec sp_addextendedproperty N'MS_Description', N'ID注释', N'user', N'dbo', N'table', N'test1', N'column', N'ID' GO

2.目标：先查询一下test1表格和字段的注释

select a.value,a.*,'---------',o.*
from sysproperties a--sysproperties.value存储了表格和字段的说明
inner join sysobjects o on a.id=o.id and o.name='test1' and o.xtype='U'--id表示这个对象的编号，'U'表示这个对象的是用户表

查询结果如下：可以看到value中不但查出了表格的注释还查询出了字段的注释而且id是一样的，说明字段和表共用一个编号，那么如何区别是表格的注释还是字段的注释呢？，乖乖，原来type字段等于3表示是表格的注释，4表示是字段的注释，我们已经把sysproperties和sysobjects全部的字段都查询出来了，但是却看不到字段的名字，查询字段对应注释就成为了下一个目标

laolaowhn

value	id	smallid	type	name	value	Expr2	name	id	xtype	uid	info	status	base_schema_ver	replinfo	parent_obj	crdate	ftcatid	schema_ver	stats_schema_ver	type	userstat	sysstat	indexdel	refdate	version	deltrig	instrig	updtrig	seltrig	category	cache
test1 表注释	517576882	0	3	MS_Description	test1 表注释	---------	test1	517576882	U	1	27	1610615063	96	0	0	2009-9-29 16:07	0	96	0	U	1	8307	0	2009-9-29 16:07	0	0	0	0	0	16904	0
ID 注释	517576882	1	4	MS_Description	ID 注释	---------	test1	517576882	U	1	27	1610615063	96	0	0	2009-9-29 16:07	0	96	0	U	1	8307	0	2009-9-29 16:07	0	0	0	0	0	16904	0

3.目标查询字段对应的注释：

select c.name,a.value, a.*,'________',o.*,'________',c.*
from   SysProperties   a      --sysproperties。value存储了表格和字段的说明
        inner   join   sysobjects o on a.id=o.id--存储了全部对象的编号名称等信息
     inner    join   syscolumns   c   on   a.id=c.id   and   a.smallid=c.colid --id表示这个对象的编号，colid表示列的编号，说明c.id还是存储了表格的编号
where o.name   =   'test1'

查询结果如下：可以看到 a.smallid=c.colid把表的注释过滤掉了，

name

value

smallid

type

name

value

Expr3

name

xtype

uid

info