SQL Server: Checking the datatype of a column

sql-server-2008 sqldatatypes
user1274655 picture user1274655 · Apr 27, 2012 · Viewed 39.5k times · Source

How to check if a column in a table has a specific datatype?

For example, how to check if a column in SQL Server table is of datatype char(11)?

Answer

juergen d picture juergen d · Apr 27, 2012 
select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'char'
and CHARACTER_MAXIMUM_LENGTH = 11
and TABLE_NAME = 'your_table'

using syscolumns:

SELECT name FROM SYSCOLUMNS
where length = 11
and xtype = 175 --char type

Related questions

How to display two digits after decimal point in SQL Server

I have table which has a column of float data type in SQL Server I want to return my float datatype column value with 2 decimal places. for ex: if i insert 12.3,it should return 12.30 if i insert 12,it should return 12.00

Read More
Is it possible to change the datatype of a column in a view?

Usually I run a script like this: ALTER TABLE [TABLE] ALTER COLUMN [Column] NVARCHAR(40); The result is that the field in the table gets converted to nvarchar. But what is the syntax for doing the same thing for a view? …

Read More
which datatype to use to store a mobile number

Which datatype shall I use to store mobile numbers of 10 digits (Ex.:9932234242). Shall I go for varchar(10) or for the big one- the "bigint". If the number is of type- '0021-23141231' , then which datatype to use?

Read More