如何获取字段类型
比如我有一个表tbl
其中有个字段col1，类型为float，
现在我要获取表tbl中字段col1的类型，返回float
怎么写sql
[解决办法]
函数？
[解决办法]
看看下面的程序,你可能就知道了:
create table tb(a int,b float,c decimal(10,2),d varchar(10))
go
select name,system_type_id from sys.columns where object_id=object_id('tb')
/*
name system_type_id
-------------------------------------------------------------------------------------------------------------------------------- --------------
a 56
b 62
c 106
d 167
(4 行受影响)
*/
create table tb(a int,b float,c decimal(10,2),d varchar(10))
go
select convert(varchar(10),name)name,system_type_id from sys.columns where object_id=object_id('tb')
/*
name system_type_id
---------- --------------
a 56
b 62
c 106
d 167
(4 行受影响)
*/
select a.name,b.name from sys.columns a,sys.types b
where a.system_type_id=b.system_type_id and
object_id=object_id('tbl')
SELECT
SQL_VARIANT_PROPERTY(字段名,'BaseType') AS 類型
FROM tablename
select 字段名=a.name,字段类型=b.name from sys.columns a join sys.types b
on (a.system_type_id=b.system_type_id)
where object_id=object_id('tb','u')
/*
字段名 字段类型
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
col numeric
(1 行受影响)
create table tb1(col1 float)
select object_name(a.id) 'TableName',
a.name 'ColumnName',
b.name 'DataType'
from syscolumns a
inner join systypes b on a.xtype=b.xtype
where a.id=object_id('tb1') and a.name='col1'
TableName ColumnName DataType
---------- ------------ --------------
tb1 col1 float
CREATE TABLE t(ID INT)
INSERT t SELECT 100
go
--有數據時可用
SELECT
SQL_VARIANT_PROPERTY(ID,'BaseType') AS 類型
FROM t
select b.name
from syscolumns AS a
INNER JOIN systypes as b on a.xtype=b.xtype
WHERE a.ID=OBJECT_ID('t') AND a.Name='ID'
go
/*
tinyint
*/
