Solved

Get columns from a Select Statement

Posted on 2010-11-24
14
624 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
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 142

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
 
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 142

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 142

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 142

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now