sql - SQL Server - 返回查询的列类型

Question

给定一个查询，我想返回一个信息行，显示返回的每一列的类型。

我研究使用：

SELECT system_type_name 
FROM 
sys.dm_exec_describe_first_result_set
('select * from table', NULL, 0) ;

这给出了我想要的简单选择语句：

但是，当将它用于我要运行的选择语句时，会出现以下错误：

有哪些替代方法可以获取查询结果集的列？

标签： sqlsql-server

Answer 1

我认为您需要以下两个查询之一

用于数据类型和长度

 use yourdatabasename;



 SELECT c.name AS 'Column Name',
       t.name + '(' + cast(c.max_length as varchar(50)) + ')' As 'DataType',
       case 
         WHEN  c.is_nullable = 0 then 'null' else 'not null'
         END AS 'Constraint'
  FROM sys.columns c
  JOIN sys.types t
    ON c.user_type_id = t.user_type_id
   WHERE c.object_id    in ( Object_id('ordertable'), Object_id('Course'))

---这里ordertable，当然是两个表名

---

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')