Search code examples
sqlsql-serversql-server-2008alter

Find out if column has varchar 20

Goal:
Find out if column Firstname has varchar(20). If true, alter the table etc.

Problem:
I have problem to find out if column Firstname has varchar 20 inside of (if Exists) Everything take place in SQL server 2008 R2

table Staff
Column: 

Firstname varchar(20)  
Lastname varchar(100)
if Exists()   // Find out if column Firstname has varchar(20)
begin 

   // Alter table and its specific column

end
Solution

  • The COLUMNS information schema view is ideal for this.

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = [table] AND COLUMN_NAME = [column]
AND DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = 20