创建变量表并检索@COLUMN_NAME

Question

我想检索表的@COLUMN_NAME作为变量。我使用 SQL Server Management Studio 2016。

declare @tmp TABLE ([TABLE_CATALOG] varchar(128), [TABLE_SCHEMA] varchar(128), [TABLE_NAME] varchar(128), [COLUMN_NAME] varchar(128), [ORDINAL_POSITION] varchar(128),[COLUMN_DEFAULT] varchar(128), 
 [IS_NULLABLE] varchar(128), [DATA_TYPE] varchar(128), [CHARACTER_MAXIMUM_LENGTH] varchar(128), [CHARACTER_OCTET_LENGTH] varchar(128), [NUMERIC_PRECISION] varchar(128), [NUMERIC_PRECISION_RADIX] varchar(128), [NUMERIC_SCALE] varchar(128), 
 [DATETIME_PRECISION] varchar(128), [CARACTER_SET_CATALOG] varchar(128), [CARACTER_SET_SCHEMA] varchar(128), [CARACTER_SET_NAME] varchar(128), [COLLATION_CATALOG] varchar(128), 
 [COLLATION_SCHEMA] varchar(128), [COLLATION_NAME] varchar(128), [DOMAIN_CATALOG] varchar(128), [DOMAIN_SCHEMA] varchar(128), [DOMAIN_NAME] varchar(128) )

INSERT INTO  @tmp 
EXEC sp_executesql (' SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'maTable' ')
DECLARE @COLUMN_NAME varchar(128)
SET @COLUMN_NAME =(SELECT COLUMN_NAME FROM @tmp )

这是我得到的错误==>

Msg 102，级别 15，状态 1，第 9 行 "maTable"附近的语法不正确。

即使我之前在其他代码中尝试过相同的语法。有人有想法吗？谢谢

Answer 1

你不需要写

EXEC sp_executesql (' SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'maTable' ')`

你可以写

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'maTable'