Get field names from union operator (expand asterisk)
I have been making quite long joins in ad hoc queries lately and it makes me very tired of typing all the field names in the select clause after my join. Is there a quick way to just list all the field names in a combined query? Is there any query to execute against a select statement or key command to do this?
For example, in the concatenation below, there are about 30 fields possible. If I could get a quick list of them to go from the "*" star, then I could take away what I don't need.
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
)
source to share
It turns out there is a plugin for that! The ApexSQL Plugin Refactor has this for free.
Direct link here: http://www.apexsql.com/sql_tools_refactor.aspx
Described by the venerable Pinal Dave here: http://blog.sqlauthority.com/2014/07/24/sql-server-how-to-format-and-refactor-your-sql-code-directly-in-ssms-and- visual-studio /
source to share
something like the following (tsql version, idea is to use result set metadata) with cursor c being your query
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
source to share