SQL Server 2000-system table and System View

I. System Table

For more information about the data dictionary, see SQL Server Bol., Only a part is listed here.

1.1, Sysservers

1. view all local servers and linked servers

Select * from Master... sysservers

1.2, Sysdatabases

1: Query all databases not created by SA

Select * from Master .. sysdatabases

Where sid not in (select Sid from Master .. syslogins where name = 'sa ')

-- Or

Select dbid, name as db_name from Master .. sysdatabases

Where sid <> 0x01

1.3, Sysobjects

1: obtain all user tables in the current database

Select name from sysobjects where xtype = 'U' and status> 0

Why should I add status> 0 because the table dtproperties, although the xtype of the table is u, is actually a system table.

The dtproperties table stores the relational graph. If no relational graph is created, it is null.

Note: This table is only the attribute of the database object. If you want to view the detailed attributes of the table and index, view sysindexes.

2: view all stored procedures in the current database

Select name as stored procedure name from sysobjects where xtype = 'p' and status> 0

Why should I add status> 0 to remove the system stored procedures in the current database.

Note: What is the difference between type and xtype in the system table?

Type exists in SQL Server 6.0 and xtype exists in SQL Server 7.0. type is reserved only for backward compatibility. For the types of each database object, see SQL Server Bol.

1.4, Syscolumns

1: Get all fields of a table or view, all parameters of a stored procedure or function

Select name from syscolumns where id = object_id ('table name ')

1.5, Sysproperties

1: how to read the table design and Table comments in SQL Server

-- All Table comments are in sysproperties.

Select B. Name, value from sysproperties as A, sysobjects as B

Where a. ID = B. ID and B. Name = 'table name'

1.6, Sysindexes

1: Quickly query the number of rows of a table based on the clustered Index

Select rowcnt, indid from sysindexes where id = object_id ('tablename') and indid <2

Note: This method may be inaccurate, because the system's statistics are not necessarily accurate in some cases. For statistical maintenance (DBCC updateusage (), see SQL server performance optimization-query optimization series.

2: View index table information

Select

Table_name = sysobjects. Name,

Index_name = sysindexes. Name,

Type = sysobjects. type,

Allocate index page = sysindexes. reserved,

Use index page = sysindexes. used,

Leaf layer page = sysindexes. dpages,

Non-leaf layer page = sysindexes. used-sysindexes.Dpages,

Rows = sysindexes. rowcnt

From sysindexes left Outer Join sysobjects on sysindexes. ID = sysobjects. ID

Where sysindexes. indid> 0 and sysindexes. indid <255 and sysindexes. Status & 64 = 0

Note: If the number of pages on the non-leaf layer is negative, it is best to run DBCC updateusage ('dbname', 'tbname', 'ixname') to update the sysindexes information.

1.7, Syslogins

SQL Server server logon information, such as SA. For details about logon, user, and role, see SQL Server 2000 management-security-user permissions.

Select * From syslogins

Select * From sysxlogins

Sysxlogins is a lite version of syslogins. It is not described in the BOL and is not recommended.

 

1.8, Sysprocesses

1: view User Process Information

Select spid, uid, syslogins. Name, login_time, net_address from sysprocesses, syslogins where sysprocesses. Sid = syslogins. Sid

2: view the database startup time

Select convert (varchar (30), login_time, 120) from Master .. sysprocesses where spid = 1

1.9, Sysdepends

1: View views, stored procedures, and functions related to a table

Select * From sysdepends where depid = object_id ('table name ')

-- Or

Select a. * From sysobjects A, syscomments B where a. ID = B. ID and B. Text like '% table name %'

Note: This query method applies only when the with encryption option is not available, that is, the object is not encrypted.

-- Or

Sp_depends

Note: The statistical information of this table is not accurate. There is no good way to do this. The query results can only be used for reference.

1.10, Sysmessages

Internal errors returned by SQL Server are here. You can add errors by yourself. However, I am used to creating a new wrong table to define errors in my program.

Select * from Master .. sysmessages where error = 5037

1.11, Sysfiles, Sysfilegroups

1. query the File Usage of the current database

Select name, filename, size/128 as 'used (m) ', Case maxsize/128 When 0 then' no limit 'else cast (maxsize/128 as varchar (10 )) end as 'total (m) 'from sysfiles

2. query the file group of the table in the current database

Select distinct A. ID, A. Name, B. groupid, C. groupname from sysobjects a inner join sysindexes B on A. ID = B. ID

Inner join sysfilegroups C on B. groupid = C. groupid

Where a. xtype = 'U' and A. Status> 0 order by A. Name

Sysfiles1 is a lite version of sysfiles. It is not described in the BOL and is not recommended.

Ii. System View

There are information_schema and system_function_schema users in the master database. Their logon is <none>, which is the two built-in users in the system.

Information_schemaIt has its own view, which is not widely used in SQL Server 2000, because it is often possible to obtain the expected results from the system table, and also to SQL Server 2005, it is still widely used in SYS. For more information about SQL Server 2005, see the subsequent series of articles in SQL Server 2005. Example:

1. query which fields of a table cannot be blank

Select column_name from information_schema.columns

Where is_nullable = 'no' and table_name = 'stb _ user'

2. query the key constraints of a table

Select * From information_schema.key_column_usage

Where table_name = 'stb _ user'

Note: key constraints refer to constraints except check and not null, that is, PK, FK, unique, and default are not constraints.