本代码适用于: 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
)
begin
insert into @tmptb
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 ( @tablename )
return
end
-- 返回一个表的列信息
-- 用法: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
)
begin
insert into @tmptb
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 ( @tablename )
return
end