table, field SQL statements in the MSSQL database _mssql

MSSQL Update from syntax

Copy Code code as follows:

Update a set a.columnname=b.columnname from A,b where b.id=1

1. system table sysobjects

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

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 value or DEFAULT constraint F = FOREIGN KEY constraint L = Log FN = scalar functions FS = assembly (CLR) scalar function FT = assembly (CLR) table-valued function IF = inline table function IT = internal table p = Stored Procedure /p> PC = assembly (CLR) stored procedure PK = PRIMARY KEY constraint (type K) RF = copy 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 UQ = U Nique constraint (type K) V = view X = extended stored procedure
Uid	smallint	The schema ID of the owner of the object. For databases upgraded from legacy SQL Server, the schema ID equals the user ID of the owner. Important NOTE: If you use any of the following SQL Server DDL statements, you must use the sys.objects catalog view instead of the sys.sysobjects. CREATE | ALTER | DROP USER CREATE | ALTER | DROP Role CREATE | ALTER | DROP application role CREATE SCHEMA ALTER AUTHORIZATION on OBJECT If the number of users and roles exceeds 32,767, an overflow or NULL return occurs. For more information, see Querying your SQL Server system directory .
Info	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Status	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Base_schema_ver	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Replinfo	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Parent_obj	Int	The object identification number of the parent object. For example, the table ID (if the parent object is a trigger or constraint).
Crdate	Datetime	The date the object was created.
Ftcatid	smallint	The Full-text catalog identifier for all user tables registered to use Full-text indexing, or 0 for all user tables that are not registered.
Schema_ver	Int	The version number that is incremented each time the schema of the table is changed. Always returns 0.
Stats_schema_ver	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Type	CHAR (2)	The object type. Can be one of the following values: AF = aggregate function (CLR) C = CHECK Constraint D = defaults or DEFAULT constraint F = FOREIGN KEY constraint FN = Scalar function FS = assembly (CLR) scalar functions FT = assembly (CLR) Table-valued Function IF = inline table function IT-Internal Tables K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored Procedure PC = assembly (CLR) stored procedures R = Rule RF = copy Filter stored procedure S = System table SN = synonym SQ = Service queue TA = Assembly (CLR) DML triggers TF = Table function TR = SQL DML Trigger TT = Table Type U = User Table V = view X = Extended Stored Procedure
Userstat	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Sysstat	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Indexdel	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Refdate	Datetime	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Version	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Deltrig	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Instrig	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Updtrig	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Seltrig	Int	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Category	Int	For publishing, constraints, and identities.
Cache	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.

Reference sys.sysobjects (Transact-SQL)

2.syscolumns

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

Column Name	Data Type	Description
Name	sysname	The name of the column name or procedure 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	The physical storage type in the sys.types .
Typestat	tinyint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Xusertype	smallint	ID of the extended user-defined data type. If the data type has a number greater than 32,767, it overflows or returns NULL. For more information, see Querying your SQL Server system directory .
Length	smallint	sys. The maximum physical storage length in the types .
Xprec	tinyint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
XScale	tinyint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Colid	smallint	The column ID or parameter ID.
Xoffset	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Bitpos	tinyint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Reserved	tinyint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Colstat	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Cdefault	Int	The ID of the default value for this column.
Domain	Int	The ID of the rule or CHECK constraint for this column.
Number	smallint	The number of the child procedure when the procedure is grouped. 0 = Non-procedure item
Colorder	smallint	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Autoval	varbinary (8000)	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Offset	smallint	The offset of the row in which this column is located.
Collationid	Int	The ID of the collation for the column. For non-character columns, this value is NULL.
Status	tinyint	Bitmap for the property that describes the column or parameter: 0x08 = column allows null values. 0x10 = when adding varchar or varbinary columns, an ANSI fill is performed. Keep trailing spaces for varchar columns and trailing zeros for varbinary columns. 0x40 = parameter is an OUTPUT parameter. 0x80 = column identity.
Type	tinyint	sys. The physical storage type in the types .
Usertype	smallint	ID of the user-defined data type in sys.types . If the number of data types exceeds 32767, an overflow or NULL return occurs. For more information, see Querying your SQL Server system directory .
Printfmt	varchar (255)	Identification is for informational purposes only. does not provide support. No guarantee of future compatibility.
Prec	smallint	The level of precision for this column. -1 = XML or large value type.
Scale	Int	The number of decimal places for this column. NULL = data type is not a numeric value.
IsComputed	Int	Flag indicating whether the column is a computed column: 0 = not a computed column. 1 = computed column.
Isoutparam	Int	Indicates whether the procedure 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 collation for the column. Null if it is not a character-based column.

Reference 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 to @n
 end
Close dlist
deallocate dlist