Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get columns from a Select Statement

Posted on 2010-11-24
14
Medium Priority
?
661 Views
Last Modified: 2012-05-10
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
Comment
Question by:Cyber-Storm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 400 total points
ID: 34210793
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
 

Author Comment

by:Cyber-Storm
ID: 34210828
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34211089
when you say "returned from a select statement", how do you "call" the select actually?

and what are you trying to achieve, actually?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 200 total points
ID: 34211194
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
 

Author Comment

by:Cyber-Storm
ID: 34211199
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34211249
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
 

Assisted Solution

by:Cyber-Storm
Cyber-Storm earned 0 total points
ID: 34211443
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 34211478
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
 
LVL 6

Accepted Solution

by:
subhashpunia earned 600 total points
ID: 34211667
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
 

Assisted Solution

by:Cyber-Storm
Cyber-Storm earned 0 total points
ID: 34211690
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 34211699
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
 

Author Comment

by:Cyber-Storm
ID: 34211702
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
 

Author Comment

by:Cyber-Storm
ID: 34211723
Hi Sub,

This solution is very elegant, unfortunately it's not supporting multiple joins to the same table. Really nice idea though.
0
 

Author Closing Comment

by:Cyber-Storm
ID: 34237114
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question