我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。

但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。

我该怎么做呢?

我的期望输出是:

Column name | Data type | Length | isnull | Pk

当前回答

试试这个:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE 
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'

其他回答

存储过程sp_columns返回详细的表信息。

exec sp_columns MyTable
SELECT  
   T.NAME AS [TABLE NAME]
   ,C.NAME AS [COLUMN NAME]
   ,P.NAME AS [DATA TYPE]
   ,P.MAX_LENGTH AS [Max_SIZE]
   ,C.[max_length] AS [ActualSizeUsed]
   ,CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
    ON T.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES AS P
    ON C.SYSTEM_TYPE_ID = P.SYSTEM_TYPE_ID
    AND C.[user_type_id] = P.[user_type_id]
WHERE T.TYPE_DESC='USER_TABLE'
  AND T.name = 'InventoryStatus'
ORDER BY 2
select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName1') 
          and 
      t.name like '%YourSearchDataType%'
union
(select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName2') 
          and 
      t.name like '%YourSearchDataType%')
union
(select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName3') 
          and 
      t.name like '%YourSearchDataType%')
order by tbl.name

根据一个数据库中三个不同表的搜索数据类型,搜索哪个列在哪个表中。这个查询可以扩展到'n'个表。

我有点惊讶居然没人提

sp_help 'mytable'

在SQL 2012中,你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

这将为您提供列名及其属性。