SQL statements for tables and fields in SQL Server databases

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 Constraints D = DEFAULT value or DEFAULT Constraint F = foreign key constraint L = Log FN = scalar function FS = assembly (CLR) scalar function FT = assembly (CLR) Table Value Function IF = inline Table Function 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 trigger TF = table functions TR = SQL DML trigger 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
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