tsql - 从 join 语句中获取字段名称(展开 * 星号)

Question

我最近在临时查询中构建相当冗长的连接，我发现在连接工作后在 select 语句中键入所有字段名称非常乏味。有没有一种快速的方法来列出组合查询中的所有字段名称？是否有一些针对 select 语句运行的查询，或执行此操作的关键命令？

例如，下面的join大概有30个字段。如果我能快速获得它们的列表以从“*”星展开，那么我就可以去掉不需要的东西。

SELECT *
FROM [DB].[THINGS].[QLINKS] Q
JOIN [DB].[THINGS].[POINTS] RQ
    ON Q.ID = RQ.POINT_ID
JOIN [DB].[THINGS].[REFERENCES] R
    ON RQ.POINT_ID = R.ID
JOIN SA_MEMBERSHIP.DBO.ASPNET_USERS U
    ON U.USERID = R.PERSON_ID
JOIN SA_MEMBERSHIP.DBO.ASPNET_MEMBERSHIP M
    ON M.USERID = U.USERID
WHERE NOT Q.ID IN (
        SELECT RQ.QLINK_ID
        FROM [DB].[DATA].[ENTRIES] E
        JOIN [DB].[THINGS].[REFERENCES] R
            ON E.PERSON_ID = R.PERSON_ID
        JOIN [DB].[THINGS].[POINTS] RQ
            ON R.ID = RQ.POINT_ID
        WHERE (
                ITEMKEY LIKE '102_0%'
                OR ITEMKEY LIKE '104_0%'
                )
            AND E.POINT_ID IS NULL
        GROUP BY E.PERSON_ID, LEFT(ITEMKEY, 5), R.ID, RQ.QLINK_ID
        )

Answer 1

类似于以下内容(tsql 版本，想法是使用结果集的元数据)，游标 c 是您的查询

declare c cursor for select * from sys.databases
go
open c


DECLARE @Report CURSOR;
declare @cn sysname
declare @op int
declare @ccf int
declare @cs int
declare @dts smallint
declare @cp tinyint
declare @colsc tinyint
declare @orp int
declare @od varchar(1)
declare @hc smallint
declare @cid int
declare @oid int
declare @dbid int
declare @dbn sysname

exec sp_describe_cursor_columns @cursor_return = @Report out, @cursor_source = N'global', @cursor_identity = N'c';

declare @res nvarchar(max)
set @res = '';

FETCH NEXT from @Report into @cn, @op, @ccf, @cs, @dts, @cp, @colsc, @orp, @od, @hc, @cid, @oid, @dbid, @dbn;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    set @res = @res +',' + @cn
   FETCH NEXT from @Report into @cn, @op, @ccf, @cs, @dts, @cp, @colsc, @orp, @od, @hc, @cid, @oid, @dbid, @dbn;
END

print stuff(@res, 1, 1, '')

CLOSE @Report;
DEALLOCATE @Report;
GO

close c
deallocate c