method created in SQL Server to determine if a table or column does not exist [go]

How to determine whether a column in a table exists in SQL Server

Let's start by sharing two methods in SQL Server that determine whether a column in a table exists, as in the following example:

For example, to determine whether the field C in table A has two methods:

The first of these methods

?

12345678

IF EXISTS (  SELECT 1 FROM SYSOBJECTS T1  INNER JOIN SYSCOLUMNS T2 ON T1.ID=T2.ID  WHERE T1.NAME=‘A‘ AND T2.NAME=‘C‘ )  PRINT ‘存在‘ ELSE PRINT ‘不存在‘

The second method, short and concise, is a fine classic

?

1234	IF COL_LENGTH(‘A‘, ‘C‘) ISNOTNULL  PRINT N‘存在‘ELSE  PRINT N‘不存在‘

method One:

?

1	select*  from  syscolumns  where id=object_id(‘表名‘)  andname=‘列名‘

Note: There is a description record that returns this column, no return null exists;

Method Two:

?

1	selectcount(*)  from sysobjects  a,syscolumns  b where a.id=b.id and b.name=‘flag1‘  and a.type=‘u‘ anda.name=‘T_Pro_ProductClass‘

Description: A return of 1 exists, or 0 if it does not exist

Second, SQL Server to determine whether the table, column exists, if not exist, create

One, the table does not exist to create:

?

12345678910

if not exists (select * from sysobjects where id = object_id(‘mytab‘) and OBJECTPROPERTY(id, ‘IsUserTable‘) = 1)create table mytab(  id int,  age int ,  name varchar(max),  primary key (id,age))go

Second, the column does not exist to create.

?

1	if notexists (select * from syscolumns where id=object_id(‘mytab‘) and name=‘columnname‘) alter table [mytab] addcolumnname nvarchar(max)

Summarize

The above is the entire content of this article, I hope that the content of this article on everyone's study or work can bring certain help, if you can message the question of communication, thank you for the script home support.

Source: http://www.jb51.net/article/107305.htm

method created in SQL Server to determine if a table or column does not exist [go]