SQL语句来获取一个表的所有列的信息，如，列名、类型、长度等

本代码适用于: SQLSERVER2000-2019SQL语句如下：select c.name, t.name as type, c.length,(case t.namewhen 'nvarchar' then c.length/2when 'nchar' then c.length/2else c.lengthend)as reallengthfrom syscolumns c join sy

Laaguid

3403人浏览 · 2022-05-17 10:07:37

Laaguid · 2022-05-17 10:07:37 发布

本代码适用于: SQLSERVER2000-2019
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('表名')

-- 加了这句代码，可以使列按设计时的顺序输出

order by colorder ASC

为了方便使用，可将其封装成一个函数代码如下：

--
-- 返回一个表的列信息
-- 用法：select * from GetTableColumnsInfo('表名')
-- laaguid 2022.05.17
--
create function GetTableColumnsInfo(@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

效果图：

查看函数详细信息：

函数：sp_helptext、sp_help