• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

Get columns from a Select Statement

Hi there,

Is there a way in T-SQL to get the list of Columns used/returned from a SELECT statement?

If I have a statement like: SELECT * FROM [tablename], then I want to be able to get the columns it'll be returning to me.

I've seen some PL-SQL stuff but nothing relating to T-SQL.

Thanks,
Storm
0
Cyber-Storm
Asked:
Cyber-Storm
7 Solutions
 
Rajkumar GsSoftware EngineerCommented:
select column_name from information_schema.columns 
 where table_name = 'tbl_employee_details' 

Open in new window


This wil return the columns in the table 'tbl_employee_details'

Raj
0
 
Cyber-StormAuthor Commented:
Hi Raj,

I'm sorry, I tried to not overdo the explanation, but let me elaborate then.  The statement listed is very basic, but if I use aliases and joins this isn't possible anymore.  I actually want to get the column names so that I can reference the INFORMATION_SCHEMA.COLUMNS after I have the list of columns to get the column information.  If the column names were listed in the SELECT statement I could parse it but I want to create a stored procedure that will be able to get the column details irrespective of whether it's a select * from table or select x, y, z from table or select a.x, a.y, b.k, b.j from table inner/outer join, etc.

In Oracle there are some functions that can assist here, but I'm looking for something specific to MSSQL.

Thanks,

Storm
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
when you say "returned from a select statement", how do you "call" the select actually?

and what are you trying to achieve, actually?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LowfatspreadCommented:
i'm with angeliii in wonderdering what you actually trying to achieve....

but i think the only way you actually be able to do this is to create the select statement as a view , and then query the information schema table for the view you have created....


 
0
 
Cyber-StormAuthor Commented:
Hi Angel,

I'm trying to get the columns that executing a select statement (irrespective of it's structure) would return.  So where you get the column headers with a returned table of data, I don't want the data, I want the columns that the select statement returns.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you have the select statement stored in a @variable?

still, what are you trying to achieve?
possibly, creating a view from that sql will help, as once the select is created as view, you can check the system dictionary views for the column names returned (without actually running the view/select)

means: you could create the view temporarily, check the column names returned, and drop the view ...
0
 
Cyber-StormAuthor Commented:
Finally found something, found this at:
http://www.eggheadcafe.com/software/aspnet/32159064/how-to-get-column-metadata-from-sql-select-statement.aspx

It's 99% what I'm looking for

GO
CREATE PROCEDURE ExecuteSQL(@SQL nvarchar(max))
AS
IF OBJECT_ID('TempProc') IS NOT NULL DROP PROCEDURE TempProc
EXEC ('CREATE PROCEDURE TempProc AS '+@SQL)
EXEC TempProc
SELECT OBJECT_NAME(referenced_major_id) as TableName,
COL_NAME(referenced_major_id,referenced_minor_id) as ColumnName,
p.value as ColumnDescription
FROM sys.sql_dependencies d LEFT JOIN sys.extended_properties p
ON d.referenced_major_id=p.major_id AND
d.referenced_minor_id=p.minor_id AND p.class=1
WHERE object_id=OBJECT_ID('TempProc')
DROP PROCEDURE TempProc
GO

EXEC ExecuteSQL 'SELECT * FROM Production.Product'

EXEC ExecuteSQL 'SELECT p.Name, (SELECT SUM(OrderQty) FROM
Sales.SalesOrderDetail d WHERE d.ProductID=p.ProductID) as TotalQty
FROM Production.Product p'

I'll post my final solution once I'm done or if I get stuck again
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
creating a procedure or a view basically is the same path, I would say :)
anyhow, the issue seems to be that you want to get both the original table/column name as well as the resulting alias...
however, for an expression using multiple columns/tables and/or constants, the info will not be given in the dependencies, from what I know ...
0
 
subhashpuniaCommented:
Use the temp table as below:

select * into #t from t1 where 1=2
select col.name as Column_Name, systypes.name Data_Type from tempdb..syscolumns col
inner join systypes on col.xtype=systypes.xtype
where col.id = object_id('tempdb..#t')
0
 
Cyber-StormAuthor Commented:
yeah, I understand that, I'm looking for the most elegant solution.  The way I see it we agree that some temp object needs to be created.  So furthing that, which would be the least taxing on the server? The temp view, temp sp or maybe even a temp table?  I'm not a guru so I don't know which is why I'm turning to you guys to assist me in implementing the most professional solution possible.

Here is what I have as my final solution (based on my mediocre knowledge), it works perfectly on select * or select x, y, z whether they be on a single table or on multiple joins.  The only catch is that if you use an alias on a table (like when you're joining the same table twice) then it only lists out the table once so I'd need to add logic to handle that unless maybe someone has any ideas?
CREATE PROCEDURE GetColumnInfo(@SQL nvarchar(max))
AS
	IF OBJECT_ID('TempProc') IS NOT NULL DROP PROCEDURE TempProc
	EXEC ('CREATE PROCEDURE TempProc AS ' + @SQL)
	
	SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, 
	COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity, 
	DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, 
	CHARACTER_SET_NAME, COLLATION_NAME
	FROM sys.sql_dependencies
	INNER JOIN INFORMATION_SCHEMA.COLUMNS ON 
	(
		TABLE_NAME = OBJECT_NAME(referenced_major_id) 
		AND COLUMN_NAME = COL_NAME(referenced_major_id,referenced_minor_id)
	)
	WHERE object_id=OBJECT_ID('TempProc')

	DROP PROCEDURE TempProc
GO

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
temp table is no good unless you want the sql to be actually run

using the procedure or a view will just to 1 action: compile the sql and store the object info.
0
 
Cyber-StormAuthor Commented:
Thanks Sub,

Unfortunately I can't guarantee that I'll have access to the Temp DB so if I create any temp items, they must be on the DB I'm working with.

I'm looking into your idea though, just wanted to reply quickly since I saw I posted after you
0
 
Cyber-StormAuthor Commented:
Hi Sub,

This solution is very elegant, unfortunately it's not supporting multiple joins to the same table. Really nice idea though.
0
 
Cyber-StormAuthor Commented:
Thanks guys, I've reworked some of my source code and my stored proc and managed to come up with a winning solution based on the selected answers.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now