SQL Server Add Delete field to determine whether a table or field exists

Add Field

ALTER TABLE DOCDSP add Dspcode char (200)

Delete a field

ALTER TABLE table_name DROP COLUMN column_name

Modify a field type

ALTER TABLE table_name ALTER COLUMN COLUMN_NAME NEW_DATA_TYPE

Renamed

sp_rename

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

Grammar

sp_rename [@objname =] ' object_name ',

[@newname =] ' New_name '

[, [@objtype =] ' object_type ']

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

--Assume the table name to be processed is: TB

--Determine if there is a primary key in the table to add the column

if exists (select 1 from sysobjects where parent_obj=object_id (' TB ') and xtype= ' PK ')

Begin

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

--Add a column of type int with a default value of 0

ALTER TABLE TB add column name int default 0

End

Else

Begin

No primary key in print ' table, add primary key column '

--Add a column of type int with a default value of 0

ALTER TABLE TB add column name int primary key default 0

End

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

To determine if the Name field exists in the Table1

if exists (SELECT * from syscolumns where id=object_id (' table1 ') and name= ' name ') begin

SELECT * from people;

End

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

If a real table can be used

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 if table exists

If a temporary table can be used

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

drop TABLE # #temp

Description, if you use the Find Real table method to hit the temporary table will not be found. Publish a difference to the generation.

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

Get a description of a table field

I usually use this view

Create View Fielddesc

As

Select O.name as Oname, c.name as Cname,convert (varchar (), p.value) as value,p.smallid as psmallid,t.name as Tname

From syscolumns C

Join systypes 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 '

When querying:

Select * from Fielddesc where oname = ' Your table name '

Note : Please pay attention to the Triple tutorial channel for more wonderful tutorials,