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

Source: Internet
Author: User

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,

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.