SQL2000系统表
SQL2000系统表
sql2000技术内幕
System Tables
As I've mentioned, SQL Server maintains a set of tables that store information about all the objects, datatypes, constraints, configuration options, and resources available to the SQL Server. This set of tables is sometimes called the system catalog. We've looked at a few such tables already, in particular sysconstraints and sysobjects. Some of the system tables exist only in the master database and contain systemwide information, and others exist in every database (including master) and contain information about the objects and resources belonging to that particular database.
The most common way of identifying a system table is by its name. All system tables start with the three characters sys, but that doesn't mean that everything that starts with sys is a system table. Another way of identifying system tables is by their object_id, which is always less than 100. A third way of identifying system tables is to look at the type column in sysobjects; system tables have a type of S. In SQL Server 2000, some of the objects that were system tables in previous versions have become views. You can get a list of these views by querying the sysobjects table directly, as follows:
SELECT爊ame燜ROM爏ysobjects WHERE爐ype�=�'V' AND爊ame燣IKE�'sys%'
If you run this query in the master database, you'll see six tables returned, and in all user databases you'll see only two. The views syslogins, sysremotelogins, sysopentapes, and sysoledbusers do not exist in the user databases.
Another type of system table isn't really a table at all, but a pseudo-table. These tables take no space and are not stored permanently on disk. Rather, SQL Server builds them dynamically every time you query them. In most cases, it doesn't make sense to store these tables on disk because their values change constantly and exist only while the server is running.
For example, storing syslockinfo on disk would be meaningless because locks do not exist unless SQL Server is running. In addition, lock information needs to be accessed so often and so quickly that storing it in a table would be much too slow. The information is actually stored in internal memory structures and is displayed as a table only when requested. You can select from syslockinfo as if it were a table, using the following statement:
SELECT�*燜ROM爉aster..syslockinfo
In addition, the stored procedure sp_lock retrieves information from syslockinfo, treating it just like any other system table. You can select from any of the tables that are really pseudo-tables, and most of them have one or more associated stored procedures for accessing their information.
Prior to SQL Server 2000, you could find out which tables were pseudo-tables by looking in the sysindexes table, which is where actual storage information is kept. There is a separate sysindexes table in every database, and every table and every index has a row in sysindexes. The rows in this table indicate where the data for the table or index is stored and how much space it takes up. Every table has a row in sysindexes with an indid (index ID) value of either 0 or 1. If the indid value is 0, the table has no clustered index and is called a heap. The value in the name column is the name of the table. If the indid value is 1, the table has a clustered index and the name of the clustered index is stored in the name column. The value in the id column is the ID of the table itself. Every nonclustered index on a table has a row in sysindexes with an indid value between 2 and 250. To see how much space the table is using, all you need to do is look at the value in the column called dpages in the row for the table. Any value greater than 0 means the table is actually taking some space.
The following query returns space usage information for all the tables with an object ID of less than 100, which generally means that it is a system table.
SELECT爊ame�=燙ONVERT(CHAR(30),爋.name),爎ows,燿pages,爋.id,爐ype FROM爏ysindexes爄燡OIN爏ysobjects爋燨N爋.id�=爄.id WHERE爋.id�<�100燗ND�(indid�=�0燨R爄ndid�=1)
A pseudo-system table is one that doesn't take any space. You might be tempted to look at the number of rows to determine whether a table takes up any space, but that might not give you the right answer. In the master database on my SQL Server, both sysreferences and sysfulltextcatalogs have 0 rows just because I haven't used those tables yet. In earlier versions of SQL Server, you could tell that a table was a real table by looking at the value in the dpages column. As soon as a table was created, SQL Server would allocate at least two pages for it, even without inserting any rows. In SQL Server 2000, as you saw earlier in this chapter, that just doesn't happen. The only way to tell whether a table is a pseudo-table is to look at the 11th bit (with a value of 1024) in the sysstat column of sysobjects. You can perform a bit-wise AND operation of sysobjects.sysstat & 1024, which in binary would be all zeros except for the 11th bit. Since 0 and anything is 0, all the bits except the 11th will be zero in the output. If the 11th bit is also zero, the result will be 0, but if the 11th bit is 1, it will stay 1 in the output and the result will be 1024. The following query will show this:
SELECT爊ame�=燙ONVERT(CHAR(30),爋.name),爎ows,牋 牋牋PseudoTable�=爋.sysstat�&�1024,爋.type FROM爏ysindexes爄燡OIN爏ysobjects爋燨N爋.id�=爄.id WHERE爋.id�<�100燗ND�(indid�=�0燨R爄ndid�=�1)
The result rows that come back from the query that have a value of 1024 for the column labeled PseudoTable are the pseudotables. Here are the pseudotables tables in my master database:
sysindexkeys牋牋牋牋牋牋牋牋牋 sysforeignkeys牋牋牋牋牋牋牋牋 sysmembers牋牋牋牋牋牋牋牋牋牋 sysprotects牋牋牋牋牋牋牋牋牋� sysperfinfo牋牋牋牋牋牋牋牋牋� sysprocesses牋牋牋牋牋牋牋牋牋 syslocks牋牋牋牋牋牋牋牋牋牋牋 syscurconfigs牋牋牋牋牋牋牋牋� syslockinfo牋牋牋牋牋牋牋牋牋� syscursorrefs牋牋牋牋牋牋牋牋� syscursors牋牋牋牋牋牋牋牋牋牋 syscursorcolumns牋牋牋牋牋牋牋 syscursortables牋牋牋牋牋牋牋� syscacheobjects牋牋牋牋牋牋牋� sysfiles
In the pubs database, there are only five pseudotables:
sysindexkeys牋牋牋牋牋牋牋牋牋 sysforeignkeys牋牋牋牋牋牋牋牋 sysmembers牋牋牋牋牋牋牋牋牋牋 sysprotects牋牋牋牋牋牋牋牋牋� sysfiles
There's actually a much easier way to find out if a table is a pseudotable, without doing all this fancy bit arithmetic. SQL Server provides a whole suite of property functions, many of which I'll tell you about in relevant places throughout this book. The function OBJECTPROPERTY has a parameter TableIsFake. The first parameter of this function is an object ID, which you can get by using the OBJECT_ID function. The following statement returns the TableIsFake value for the syslockinfo and sysobjects tables in the master database:
USE爉aster SELECT燨BJECTPROPERTY(OBJECT_ID('syslockinfo'),�'TableIsFake'), 牋牋OBJECTPROPERTY(OBJECT_ID('sysobjects'),�'TableIsFake') RESULTS: 牋牋牋牋牋牋牋牋牋牋牋牋 -----------�-----------� 0
The return value of 1 means that the table is a pseudotable, and the return value of 0 means that it is not. The function will return a NULL value if the table doesn't exist or if you misspell the name of the property.
Microsoft does not recommend that you directly query the system tables at all. The online documentation states:
The structure of the system catalog tables is dependent on the underlying architecture of SQL Server and changes from one version to another. Even an application that only issues SELECT statements may have to be at least partially rewritten when migrating to a new version of SQL Server if it directly queries system tables that change or are not present in the new version.
So, if you aren抰 supposed to directly access the system tables, how are you supposed to find out information about your system? SQL Server 2000 provides a number of tools for accomplishing this. We took a look in the preceding section at one of many possible property functions. There are also system stored procedures, such as sp_help and sp_helpconstraint. I抣l tell you more about system procedures in Chapter 11. Finally, in compliance with the ANSI SQL Standard, there is a set of INFORMATION SCHEMA VIEWS, some of which we抣l look at later in the book.
系统表
sysindexkeys
sysforeignkeys
sysmembers
sysprotects
sysperfinfo
sysprocesses
syslocks
syscurconfigs
syslockinfo
syscursorrefs
syscursors
syscursorcolumns
syscursortables
syscacheobjects
sysfiles
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战