Solved

Get columns from a Select Statement

Posted on 2010-11-24
14
644 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 100 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 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 200 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 150 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 200 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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