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)?
例如，如何检查SQL Server表中的列是否为数据类型char（11）？
2 个解决方案
#1
19
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'char'
and CHARACTER_MAXIMUM_LENGTH = 11
and TABLE_NAME = 'your_table'
using syscolumns:
使用syscolumns：
SELECT name FROM SYSCOLUMNS
where length = 11
and xtype = 175 --char type
#2
3
select case when DATA_TYPE= 'char' then 'T' else 'F' end,
case when CHARACTER_MAXIMUM_LENGTH = 11 then 'T' else 'F' end
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'MY_COLUMN_NAME'
and TABLE_NAME = 'MY_TABLE_NAME'
#1
19
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'char'
and CHARACTER_MAXIMUM_LENGTH = 11
and TABLE_NAME = 'your_table'
using syscolumns:
使用syscolumns：
SELECT name FROM SYSCOLUMNS
where length = 11
and xtype = 175 --char type
#2
3
select case when DATA_TYPE= 'char' then 'T' else 'F' end,
case when CHARACTER_MAXIMUM_LENGTH = 11 then 'T' else 'F' end
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'MY_COLUMN_NAME'
and TABLE_NAME = 'MY_TABLE_NAME'