The method used to determine whether a table or column does not exist in SQL Server.

The method used to determine whether a table or column does not exist in SQL Server.

1. How to determine whether a Column exists in the Table in SQL Server

First, I would like to share with you two methods for determining whether a Column exists in the Table in SQL Server:

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

Method 1

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 'does not exist'

The second method is short and concise.

IF COL_LENGTH ('A', 'C') is not null print n' existence 'else PRINT n' nonexistent'

Method 1:

Select * from syscolumns where id = object_id ('table name') and name = 'column name'

Note: If yes, a description Record of this column is returned. If no description record exists, null is returned;

Method 2:

select  count(*)  from  sysobjects  a,syscolumns  b where a.id=b.id and b.name='flag1' and a.type='u'  and  a.name='T_Pro_ProductClass' 

NOTE: If 1 exists, 0 is returned if no exists.

Ii. Check whether tables and columns exist in SQL Server. If not, create

1. Create a table if the table does not exist:

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

2. Create if the column does not exist.

if not exists (select * from syscolumns where id=object_id('mytab') and name='columnname') alter table [mytab] add columnname nvarchar(max)

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message. Thank you for your support.