SQL Server adds and deletes a field to determine whether a table or field exists.

Add Field

Alter table docdsp add dspcode char (200)

Delete Field

Alter table table_NAME drop column column_NAME

Modify Field Type

Alter table table_name alter column column_name new_data_type

Rename

Sp_rename

Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database.

Syntax

Sp_rename [@ objname =] 'object _ name ',

[@ Newname =] 'new _ name'

[, [@ Objtype =] 'object _ type']

========================================================== ====================

-- Assume that the table to be processed is named tb.

-- Determines whether the table to be added has a primary key.

If exists (select 1 from sysobjects where parent_obj = object_id ('tb') and xtype = 'pk ')

Begin

Print 'The table already has a primary key, and the column can only be added as a normal column'

-- Add an int column. The default value is 0.

Alter table tb add column name int default 0

End

Else

Begin

Print 'table with no primary key, add primary key column'

-- Add an int column. The default value is 0.

Alter table tb add column name int primary key default 0

End

/*************************************** **************************************** *******/

Determine whether the name field exists in table1

If exists (select * from syscolumns where id = object_id ('table1') and name = 'name') begin

Select * from people;

End

========================================================== ======================================

You can use

If exists (select * from sysobjects where id = object_id (n' [dbo]. [table name] ') and OBJECTPROPERTY (id, N 'isusertable') = 1) drop table [dbo]. [Table name] -- delete a table if it exists

For temporary tables

If object_id ('tempdb .. # temp ') is not null

Drop table # temp

Note: If you use the real table search method to create a temporary table, the system will not be able to find the. Release difference generation.

========================================================== ============================

Obtain the table field description.

I usually use this view

Create view fielddesc

As

Select o. name as oname, c. name as cname, convert (varchar (30), p. value) as value, p. smallid as psmallid, t. name as tname

From syscolumns c

Join policypes t on c. xtype = t. xtype

Join sysobjects o on o. id = c. id

Left join sysproperties p on p. smallid = c. colid and p. id = o. id

Where o. xtype = 'U'

Query:

Select * from fielddesc where oname = 'your table name'

Note: For more exciting tutorials, please follow the help houseTutorialChannel,