SQL SERVER – Simple Way to Find Existence of Column in a Table

来源:转载


If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.

Let us create this dataset

<span>USE </span><span>TEMPDB</span><span>;<br /> </span><span>CREATE TABLE </span><span>TESTING</span><span>(</span><span>ID </span><span>INT</span><span>, </span><span>NAME </span><span>VARCHAR</span><span>(</span><span>100</span><span>))</span>

Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods

IF EXISTS ( SELECT * FROM SYS.COLUMNS WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING') ) PRINT 'COLUMN EXISTS' -- IF EXISTS ( SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING' ) PRINT 'COLUMN EXISTS'

But did you know there is a shorter way to do this? Use COL_LENGTH system function

<span>IF </span><span>(</span><span>SELECT </span><span>COL_LENGTH</span><span>(</span><span>'TESTING'</span><span>,</span><span>'NAME'</span><span>)) </span><span>IS </span><span>NOT NULL<br /> </span><span>PRINT </span><span>'COLUMN EXISTS'</span>

What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.

It is simple and faster.

Reference: Pinal Dave ( http://blog.sqlauthority.com)



分享给朋友:
您可能感兴趣的文章:
随机阅读: