IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'Table')
      BEGIN
        SELECT COLS.COLUMN_NAME, COLS.DATA_TYPE, COLS.CHARACTER_MAXIMUM_LENGTH, 
              (SELECT 'Yes' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
                              ON COLS.TABLE_NAME = TC.TABLE_NAME 
                             AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                             AND KCU.TABLE_NAME = TC.TABLE_NAME
                             AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
                             AND KCU.COLUMN_NAME = COLS.COLUMN_NAME) AS KeyX
        FROM INFORMATION_SCHEMA.COLUMNS COLS WHERE TABLE_NAME = 'Table' ORDER BY KeyX DESC, COLUMN_NAME
      END

查找数据类型和长度的组合结果，并以“NULL”和“非空”的形式为空。

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    = Object_id('TableName')

你会发现结果如下所示。

谢谢你！

试试这个:

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

抛出了另一种在SQL server中解决问题的方法。 我的小脚本在这里应该返回列名，数据类型，是空的，约束和索引名称。 您还可以包括任何额外的列，如精度，比例… (你需要用你的数据库名、Schema name和Table name替换数据库名) .返回列的顺序与从'select * from table'中返回列的顺序相同

USE DBA -- Replace Database Name with yours

DECLARE @SCHEMA VARCHAR(MAX)
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE @SCHEMA_TABLE_NAME VARCHAR(MAX)

SET @SCHEMA = REPLACE(REPLACE('[SCHEMA NAME]', '[', ''), ']', '')--Replace Schema Name with yours
SET @TABLE_NAME = REPLACE(REPLACE('[TABLE NAME]', '[', ''), ']', '') --' Replace Table  Name with yours
SET @SCHEMA_TABLE_NAME = @SCHEMA + '.' + @TABLE_NAME;


WITH SchemaColumns
AS (
    SELECT C.COLUMN_NAME,
        IS_NULLABLE,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        C.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    WHERE C.TABLE_SCHEMA = @SCHEMA
        AND C.TABLE_NAME = @TABLE_NAME
    ),
SchemaConstraints
AS (
    SELECT CN.COLUMN_NAME,
        CC.CONSTRAINT_TYPE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CC
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CN ON CC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
    WHERE CC.TABLE_SCHEMA = @SCHEMA
        AND CC.TABLE_NAME = @TABLE_NAME
    ),
SchemaIndex
AS (
    SELECT I.name AS index_name,
        COL_NAME(IC.object_id, IC.column_id) AS column_name,
        IC.index_column_id,
        IC.key_ordinal,
        IC.is_included_column
    FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS IC ON I.object_id = IC.object_id
        AND I.index_id = IC.index_id
    WHERE I.object_id = OBJECT_ID(@SCHEMA_TABLE_NAME)
    )
SELECT ISNULL(SchemaColumns.COLUMN_NAME, '') "Column Name",
    CASE 
        WHEN SchemaColumns.CHARACTER_MAXIMUM_LENGTH IS NULL
            THEN UPPER(ISNULL(SchemaColumns.DATA_TYPE, ''))
        ELSE CONCAT (
                UPPER(ISNULL(SchemaColumns.DATA_TYPE, '')),
                '(',
                CAST(SchemaColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),
                ')'
                )
        END "Data Type",
    SchemaColumns.IS_NULLABLE "Is Nullable",
    ISNULL(SchemaConstraints.CONSTRAINT_TYPE, '-') "Constraints",
    ISNULL(STRING_AGG(CONVERT(NVARCHAR(max), SchemaIndex.INDEX_NAME), CHAR(13)), '-') "Indexes Names"
FROM SchemaColumns
LEFT JOIN SchemaConstraints ON SchemaConstraints.COLUMN_NAME = SchemaColumns.COLUMN_NAME
LEFT JOIN SchemaIndex ON SchemaColumns.COLUMN_NAME = SchemaIndex.COLUMN_NAME
GROUP BY SchemaColumns.COLUMN_NAME,
    SchemaColumns.DATA_TYPE,
    SchemaColumns.CHARACTER_MAXIMUM_LENGTH,
    SchemaColumns.IS_NULLABLE,
    SchemaConstraints.CONSTRAINT_TYPE,
    SchemaColumns.ORDINAL_POSITION
ORDER BY SchemaColumns.ORDINAL_POSITION

查询:执行sp_description _first_result_set并选择年收入 [DB_NAME]。[DBO]。(员工)”

注意:在某些ide中选择n之前是工作的，或者在某些ide中没有选择n是工作的

在SQL 2012中，你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

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