MSSQL中能否判断表字段是什么数据类型

Question

MSSQL中能否判断表字段是什么数据类型,,

ALTER function [uscost].[decode](@inputfield sql_variant,@value1 sql_variant ,@Result1 sql_variant ,@deaultResult sql_variant )
returns sql_variant
as
begin
 declare @returnValue sql_variant

 SET @returnValue=
 CASE
 WHEN @inputfield=@value1 THEN @Result1
 ELSE @deaultResult
 END
 return @returnValue
End

我建立了这个函数，能否根据 ,@Result1 字段类型。。如 字段是 numeric,那么返回 numeric,如 字段是 VARCHAR,那么返回 VARCHAR

Answer 1

sql server 中没有泛型

Answer 2

下面的代码可以查看表的信息，也可以查出字段类型，希望能帮到你

SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx

FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties  g
on a.id=g.minor_id AND a.colid = g.minor_id
--where d.name like '%' and d.name not like '%_his'
order by object_name(a.id),a.colorder

Answer 3

下面的代码可以查看表的信息，也可以查出字段类型，希望能帮到你

SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx

FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties  g
on a.id=g.minor_id AND a.colid = g.minor_id
--where d.name like '%' and d.name not like '%_his'
order by object_name(a.id),a.colorder

tim-2009大侠，人家是判断传入的参数是什么类型啊

LZ您好，就像 邹建大侠所说的， SQLSERVER里没有泛型，所以用sql_variant数据类型是行不通的

Answer 4

不是特别明白你的意思。

你可以把varchar和numeric的数据放入sql_variant里面啊。

---

想不想时已是想，不如不想都不想。

Answer 5

传入的时候，怎么知道我传入 varchar或numeric。。。我SELECT 列 from 表，，怎么知道我这个列是 varchar或numeric

Answer 6

Can use isnumeric function to check if value you got is numeric or string.

Answer 7

ALTER function [uscost].[decode](@inputfield sql_variant,@value1 sql_variant ,@Result1 sql_variant ,@deaultResult sql_variant )
returns sql_variant

现在这个函数返回 sql_variant，我能否如果是numeric 返回值 numeric ，如果是varchar 返回值varchar ?

Answer 8

您好，
您可以試一下使用「SQL_VARIANT_PROPERTY」來判斷儲存的資料型態，如下，希望對您有幫助!

DECLARE @int SQL_VARIANT, @datetime SQL_VARIANT;
SET @int = 0;
SET @datetime = GETDATE();

SELECT @int, SQL_VARIANT_PROPERTY(@int,'BaseType') 
SELECT @datetime, SQL_VARIANT_PROPERTY(@datetime,'BaseType') 

SELECT CASE SQL_VARIANT_PROPERTY(@int,'BaseType') WHEN 'int' THEN '@int data type is int' ELSE '@int data type is not int' END;

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/