SQL statements for tables and fields in SQL Server databases

Source: Internet
Author: User

1. System Table sysobjects

Each object (such as constraints, default values, logs, rules, and stored procedures) created in the database corresponds to a row.

Column name Data Type Description

Name

Sysname

Object Name

ID

Int

Object ID number

xtype

char (2)

object type. It can be one of the following object types:

AF = aggregate function (CLR)

C = check constraint

d = default value or default constraint

F = foreign key constraint

L = Log

fn = scalar function

FS = Program set (CLR) scalar functions

Ft = assembly (CLR) table valued functions

If = inline table functions

it = internal table

P = Stored Procedure

Pc = assembly (CLR) stored Procedure

PK = primary key constraint (type: K)

Rf = copy and filter the Stored Procedure

S = system table

Sn = synonym

sq = Service Queue

Ta = assembly (CLR) DML triggers

TF = table functions

TR = SQL DML triggers

TT = TABLE type

U = User table

uq = unique constraint (type: K)

V = view

X = Extended Stored Procedure

UID

Smallint

The schema ID of the object owner.

For databases upgraded from the old version of SQL Server, the schema ID is equal to the owner's user ID.

Important:If you use any of the following SQL Server DDL statements, you must use the SYS. Objects directory view instead of SYS. sysobjects.Create | alter | drop userCreate | alter | drop roleCreate | alter | drop application roleCreate SchemaAlter authorization on Object

If the number of users and roles exceeds 32,767, overflow or null is returned.

For more information, see querying the SQL server system directory.

Info

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Status

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Base_schema_ver

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Replinfo

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Parent_obj

Int

ID of the parent object. For example, table ID (if the parent object is a trigger or constraint ).

Crdate

Datetime

Object creation date.

Ftcatid

Smallint

The full-text directory identifier of all user tables registered with full-text indexes. The value is 0 for all user tables not registered.

Schema_ver

Int

The version number that is added each time you change the schema of a table. Always Returns 0.

Stats_schema_ver

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Type

Char (2)

Object type. It can be one of the following values:

AF = aggregate function (CLR)

C = check Constraints

D = default value or default Constraint

F = foreign key constraint

Fn = scalar function

FS = assembly (CLR) scalar function

FT = assembly (CLR) Table value function if = inline Table Function

It-internal table

K = primary key or unique constraint

L = Log

P = Stored Procedure

PC = assembly (CLR) Stored Procedure

R = rule

Rf = copy and filter the Stored Procedure

S = system table

Sn = Synonym

Sq = Service Queue

Ta = assembly (CLR) DML trigger

TF = table functions

Tr = SQL DML trigger

Tt = table Type

U = User table

V = View

X = Extended Stored Procedure

Userstat

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Sysstat

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Indexdel

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Refdate

Datetime

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Version

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Deltrig

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Instrig

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Updtrig

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Seltrig

Int

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Category

Int

Used for publishing, binding, and identification.

Cache

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

See SYS. sysobjects (TRANSACT-SQL)

2. syscolumns

Returns a row for each column in each table and view, and returns a row for each parameter of the stored procedure in the database.

Column name Data Type Description

Name

Sysname

Name of the column name or process parameter.

ID

Int

The object ID of the table to which this column belongs, or the ID of the stored procedure associated with this parameter.

Xtype

Tinyint

SYS. TypesPhysical storage type in.

Typestat

Tinyint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Xusertype

Smallint

The ID of the Extended user-defined data type. If the number of the Data Type exceeds 32,767, overflow or null is returned. For more information, see querying the SQL server system directory.

Length

Smallint

Sys.TypesMaximum length of physical storage in.

Xprec

Tinyint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

XScale

Tinyint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Colid

Smallint

Column ID or parameter ID.

Xoffset

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Bitpos

Tinyint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Reserved

Tinyint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Colstat

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Cdefault

Int

The ID of the default value of this column.

Domain

Int

The ID of the rule or check constraint for this column.

Number

Smallint

The sub-process ID of the Process Group.

0 = non-process item

Colorder

Smallint

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Autoval

Varbinary (8000)

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Offset

Smallint

The offset of the row in which this column is located.

Collationid

Int

The ID of the column sorting rule. This value is null for non-character columns.

Status

Tinyint

Bitmap used to describe the attributes of a column or parameter:

0x08 = columns allow null values.

0x10 = addVarcharOrVarbinaryWhen columns are created, ANSI fills are executed. RetainedVarcharTrailing spaces andVarbinaryThe trailing zero of a column.

0x40 = the output parameter.

0x80 = column ID column.

Type

Tinyint

Sys.TypesPhysical storage type in.

Usertype

Smallint

SYS. TypesThe ID of the User-Defined data type in. If the number of data types exceeds 32767, overflow or null is returned. For more information, see querying the SQL server system directory.

Printfmt

Varchar (255)

Marked as for reference only. Not supported. Future compatibility is not guaranteed.

Prec

Smallint

The precision level of this column.

-1 =XMLOr struct type.

Scale

Int

The number of decimal places in this column.

Null = the data type is not a numerical value.

Iscomputed

Int

Indicates whether a column is a flag of a calculated column:

0 = non-computed column.

1 = calculation column.

Isoutparam

Int

Indicates whether the process parameter is an output parameter:

1 = true

0 = false

Isnullable

Int

Indicates whether the column allows null values:

1 = true

0 = false

Collation

Sysname

The name of the column sorting rule. If it is not a character-based column, it is null.

See SYS. syscolumns (TRANSACT-SQL)

Delete a table in a database

declare Dlist cursor for
select name from sysobjects where xtype = 'U'
open Dlist
declare @ n varchar (4000)
fetch next from Dlist into @ n
while @ fetch_status = 0
begin
declare @ es varchar (4000)
set @ es = 'drop table' + @ n
exec (@ es)
fetch next from Dlist into @ n
end
close Dlist
deallocate Dlist

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.