Table hệ thống trong SQL Server 2008 R2 (Phần 01) – Sys.Objects


Kính gửi các bạn,

Sau đây tôi xin liệt kê các table, View hệ thống trong hệ quản trị CSDL SQL Server 2008. Nhằm giúp ích cho người lập trình cũng như người quản trị hệ thống CSDL có thể hiểu rõ và điều khiển hệ thống của mình một cách nhanh và hiệu quả nhất.

SYS : là một trong schema hệ thống của SQL Server chứa table, view hệ thống. Table Sys.Objects này lưu trữ thông tin về các loại đối tượng trong hệ quản trị CSDL. Ví dụ : khi bạn tạo một table, view, store procedure…. bằng câu lệnh DDL (Data Define Language) như : Create, các đối tượng này được lưu trữ vào table hệ thống sys.objects

Cấu trúc table Sys.Objects

Column name Data type  Description
name sysname Object name
id int Object identification number
xtype char(2) Object type. Can be one of the following object types:

AF = Aggregate function (CLR)

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = In-lined table-function

IT = Internal table

P = Stored procedure

PC = Assembly (CLR) stored-procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML triggerTF = Table function

TR = SQL DML Trigger

TT = Table type

U = User table

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure

uid smallint Schema ID of the owner of the object.

For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.

Important:
If you use any of the following SQL Server DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects.CREATE | ALTER | DROP USERCREATE | ALTER | DROP ROLECREATE | ALTER | DROP APPLICATION ROLECREATE SCHEMAALTER AUTHORIZATION ON OBJECT

Overflows or returns NULL if the number of users and roles exceeds 32,767.

For more information, see Querying the SQL Server System Catalog.

info smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
base_schema_ver int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
replinfo int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
parent_obj int Object identification number of the parent object. For example, the table ID if it is a trigger or constraint.
crdate datetime Date the object was created.
ftcatid smallint Identifier of the full-text catalog for all user tables registered for full-text indexing, and 0 for all user tables that are not registered.
schema_ver int Version number that is incremented every time the schema for a table changes. Always returns 0.
stats_schema_ver int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
type char(2) Object type. Can be one of the following values:

AF = Aggregate function (CLR)

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

FN = Scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued functionIF = In-lined table-function

IT – Internal table

K = PRIMARY KEY or UNIQUE constraint

L = Log

P = Stored procedure

PC = Assembly (CLR) stored-procedure

R = Rule

RF = Replication filter stored procedure

S = System table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = Table function

TR = SQL DML Trigger

TT = Table type

U = User table

V = View

X = Extended stored procedure

userstat smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
sysstat smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
indexdel smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
refdate datetime Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
version int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
deltrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
instrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
updtrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
seltrig int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
category int Used for publication, constraints, and identity.
cache smallint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Khi muốn tìm một đối tượng nào theo tên hoặc theo kiểu đối tượng, chúng ta có thể sử dụng câu SQL:

SELECT * from sys.objects

Tìm tất cả các table trong database

SELECT * from sys.objects where type = ‘U’

Tìm tất cả view trong database

SELECT * from sys.objects where type = ‘V’

=> Tùy thuộc vào “type” các bạn có thể tìm các đối tượng.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s