如何从SQL Server中的表中获取列名?

您可以通过查询 Information Schema views .

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

可以在所有这些DB对象上创建:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

http://msdn.microsoft.com/en-us/library/ms176077.aspx

您也可以通过SQL查询来完成。像这样的事情应该会有帮助:

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.yourTableName') 

或者一种变化是:

SELECT   o.Name, c.Name
FROM     sys.columns c 
         JOIN sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U' 
ORDER BY o.Name, c.Name

这将从所有表中获取所有列,按表名排序,然后按列名排序。

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

这总比从别人那里得到好 sys.columns 因为它显示 DATA_TYPE 直接地

你可以用 sp_help

sp_help <table_name>;

上述命令的键盘快捷键:选择表格名称(即高亮显示),然后按 中高音 + 一层楼

通过使用此查询,您可以得到以下答案:

select Column_name 
from Information_schema.columns 
where Table_name like 'table name'

您可以编写此查询以获取列名和所有详细信息,而无需使用 MySql中的信息架构:

SHOW COLUMNS FROM database_Name.table_name;

--This is another variation used to document a large database for conversion (Edited to --remove static columns)

SELECT o.Name                   as Table_Name
     , c.Name                   as Field_Name
     , t.Name                   as Data_Type
     , t.length                 as Length_Size
     , t.prec                   as Precision_
FROM syscolumns c 
     INNER JOIN sysobjects o ON o.id = c.id
     LEFT JOIN  systypes t on t.xtype = c.xtype  
WHERE o.type = 'U' 
ORDER BY o.Name, c.Name

--In the left join, c.type is replaced by c.xtype to get varchar types

SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

TABLE_NAME 这是你的桌子吗

SELECT column_name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable 
FROM information_schema.COLUMNS WHERE table_name LIKE 'YOUR_TABLE_NAME'
ORDER BY ordinal_position

您可以试试这个。这会给出所有列的名称及其各自的数据类型。

desc <TABLE NAME> ;

只要运行这个命令

EXEC sp_columns 'Your Table Name'

这个问题缺少以下方法:

-- List down all columns of table 'Logging'
select * from sys.all_columns where object_id = OBJECT_ID('Logging')

它将检查给定的 table 是 Base Table .

SELECT 
    T.TABLE_NAME AS 'TABLE NAME',
    C.COLUMN_NAME AS 'COLUMN NAME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
    WHERE   T.TABLE_TYPE='BASE TABLE'
            AND T.TABLE_NAME LIKE 'Your Table Name'

您可以使用此查询

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'

SELECT c.Name 
FROM sys.columns c
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.object_id = OBJECT_ID('TABLE_NAME')
ORDER BY c.Name

另一个可能更直观的选择是:

SELECT [name] 
FROM sys.columns 
WHERE object_id = OBJECT_ID('[yourSchemaType].[yourTableName]') 

这将在单个列中提供所有列名。 如果您关心其他元数据,可以将编辑SELECT语句更改为 SELECT * .

我可以看到很多不同的答案和方法,但这其中有摩擦,那就是 objective .

是的,目标。如果你想 only know

SELECT * FROM my_table WHERE 1=0
or
SELECT TOP 0 * FROM my_table

但是如果你想 use 那些专栏的某个地方或者只是说 manipulate 那么上面的快速查询就没有任何用处了。你需要使用

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'

还有一种方法可以知道某些特定的列,其中我们需要一些类似的列

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'name_of_your_table'

SELECT TOP (0) [toID]
      ,[sourceID]
      ,[name]
      ,[address]
  FROM [ReportDatabase].[Ticket].[To]

您可以尝试使用:-

USE db_name;
DESCRIBE table_name;

它将为您提供具有该类型的列名。

一些SQL生成SQL:

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  col001 INTEGER
, col002 INTEGER
, col003 INTEGER
, col004 INTEGER
, col005 INTEGER
, col006 INTEGER
, col007 INTEGER
, col008 INTEGER
, col009 INTEGER
, col010 INTEGER
)
;
INSERT INTO test(col001) VALUES(1);
INSERT INTO test(col002) VALUES(1);
INSERT INTO test(col005) VALUES(1);
INSERT INTO test(col009) VALUES(1);
INSERT INTO test VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

SELECT
  CASE ROW_NUMBER() OVER(ORDER BY ordinal_position)
  WHEN 1 THEN 
    'SELECT'+CHAR(10)+'  *'+CHAR(10)+'FROM test'
   +CHAR(10)+'WHERE '
  ELSE
    '   OR '
  END
+ column_name +' IS NOT NULL'
+ CASE ROW_NUMBER() OVER(ORDER BY ordinal_position DESC)
  WHEN 1 THEN 
    CHAR(10)+';'
  ELSE
    ''
  END
  FROM information_schema.columns
  WHERE table_schema='dbo'
    AND table_name = 'test'
ORDER BY
  ordinal_position;

-- the whole scenario. Works for 10 , will work for 100, too:

-- out -----------------------------------------------
-- out  SELECT
-- out   *
-- out FROM test
-- out WHERE col001 IS NOT NULL
-- out     OR col002 IS NOT NULL
-- out     OR col003 IS NOT NULL
-- out     OR col004 IS NOT NULL
-- out     OR col005 IS NOT NULL
-- out     OR col006 IS NOT NULL
-- out     OR col007 IS NOT NULL
-- out     OR col008 IS NOT NULL
-- out     OR col009 IS NOT NULL
-- out     OR col010 IS NOT NULL
-- out ;

简单且不需要sys变量:

SHOW COLUMNS FROM suppliers;