SQL语句来获取一个表的所有列的信息，如，列名、类型、长度等
时间：2010-08-25 来源：moss_tan_jun
本代码适用于: SQLSERVER2000/2005
SQL语句如下：
select c.name, t.name as type, c.length
,(case t.name
when 'nvarchar' then c.length/2
when 'nchar' then c.length/2
else c.length
end)
as reallength
from syscolumns c join systypes t
on c.xtype=t.xtype
where t.name <> 'sysname' and c.id=object_id('Table1')
-- 加了这句代码，可以使列按设计时的顺序输出
order by colorder ASC
为了方便使用，可将其封装成一个函数代码如下：
---- 返回一个表的列信息-- 用法：select * from tbl_columns('Table1')-- zyl 2007.11.6--create function tbl_columns(@tablename nvarchar(256))returns @tmptb table( name nvarchar(256), type varchar(256), length int, reallength int)begininsert into @tmptbselect c.name, t.name as type, c.length ,(case t.name when 'nvarchar' then c.length/2 when 'nchar' then c.length/2 else c.length end) as reallengthfrom syscolumns c join systypes ton c.xtype=t.xtypewhere t.name <> 'sysname' and c.id=object_id(@tablename)return end
这是一个运行结果：
相关阅读 更多 +
