我们都知道,要从表中选择所有列,可以使用
SELECT * FROM tableA
是否有一种方法可以在不指定所有列的情况下从表中排除列?
SELECT * [except columnA] FROM tableA
我所知道的唯一方法是手动指定所有列并排除不需要的列。这真的很耗时,所以我正在寻找方法来节省时间和精力,以及未来的维护表应该有更多/更少的列。
我们都知道,要从表中选择所有列,可以使用
SELECT * FROM tableA
是否有一种方法可以在不指定所有列的情况下从表中排除列?
SELECT * [except columnA] FROM tableA
我所知道的唯一方法是手动指定所有列并排除不需要的列。这真的很耗时,所以我正在寻找方法来节省时间和精力,以及未来的维护表应该有更多/更少的列。
当前回答
是的,这是可能的(但不建议)。
CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'
DECLARE @columns varchar(8000)
SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION
EXEC ('SELECT ' + @columns + ' FROM contact')
代码说明:
Declare a variable to store a comma separated list of column names. This defaults to NULL. Use a system view to determine the names of the columns in our table. Use SELECT @variable = @variable + ... FROM to concatenate the column names. This type of SELECT does not not return a result set. This is perhaps undocumented behaviour but works in every version of SQL Server. As an alternative you could use SET @variable = (SELECT ... FOR XML PATH('')) to concatenate strings. Use the ISNULL function to prepend a comma only if this is not the first column name. Use the QUOTENAME function to support spaces and punctuation in column names. Use the WHERE clause to hide columns we don't want to see. Use EXEC (@variable), also known as dynamic SQL, to resolve the column names at runtime. This is needed because we don't know the column names at compile time.
其他回答
你可以创建一个包含你想要选择的列的视图,然后你可以从视图中选择*…
您可以使用REGEX列规范。
下面的查询选择除ds和hr之外的所有列 选择(ds |人力资源)? +。+来自销售
当我使用视图而不是实际表时,BartoszX提出的答案(存储过程)对我不起作用。
下面的想法和代码(除了我的修复)属于BartoszX。
为了使它既适用于表,也适用于视图,使用以下代码:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[select_without]
@schema_name sysname = N'dbo',
@table_name sysname,
@list_of_columns_excluded nvarchar(max),
@separator nchar(1) = N','
AS
BEGIN
DECLARE
@SQL nvarchar(max),
@full_table_name nvarchar(max) = CONCAT(@schema_name, N'.', @table_name);
SELECT @SQL = COALESCE(@SQL + ', ', '') + QUOTENAME([Name])
FROM sys.columns sc
LEFT JOIN STRING_SPLIT(@list_of_columns_excluded, @separator) ss ON sc.[name] = ss.[value]
WHERE sc.OBJECT_ID = OBJECT_ID(@full_table_name)
AND ss.[value] IS NULL;
SELECT @SQL = N'SELECT ' + @SQL + N' FROM ' + @full_table_name;
EXEC(@SQL)
END
GO
DECLARE @SQL VARCHAR(max), @TableName sysname = 'YourTableName'
SELECT @SQL = COALESCE(@SQL + ', ', '') + Name
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@TableName)
AND name NOT IN ('Not This', 'Or that');
SELECT @SQL = 'SELECT ' + @SQL + ' FROM ' + @TableName
EXEC (@SQL)
更新:
如果您经常使用这个任务,还可以创建一个存储过程来处理它。 在这个例子中,我使用了内置的STRING_SPLIT(),它在SQL Server 2016+上可用, 但如果你需要,有很多例子教你如何在SO上手动创建它。
CREATE PROCEDURE [usp_select_without]
@schema_name sysname = N'dbo',
@table_name sysname,
@list_of_columns_excluded nvarchar(max),
@separator nchar(1) = N','
AS
BEGIN
DECLARE
@SQL nvarchar(max),
@full_table_name nvarchar(max) = CONCAT(@schema_name, N'.', @table_name);
SELECT @SQL = COALESCE(@SQL + ', ', '') + QUOTENAME([Name])
FROM sys.columns sc
LEFT JOIN STRING_SPLIT(@list_of_columns_excluded, @separator) ss ON sc.[name] = ss.[value]
WHERE sc.OBJECT_ID = OBJECT_ID(@full_table_name, N'u')
AND ss.[value] IS NULL;
SELECT @SQL = N'SELECT ' + @SQL + N' FROM ' + @full_table_name;
EXEC(@SQL)
END
然后就是:
EXEC [usp_select_without]
@table_name = N'Test_Table',
@list_of_columns_excluded = N'ID, Date, Name';
不,这是不可能的。如果在您的情况下可行,也许您可以创建自定义视图
如果你的数据库支持动态sql的执行,你可以写一个SP,把你不想看到的列传递给它,让它动态创建查询并返回结果给你。我认为这至少在SQL Server中是可行的