November 2, 2009 at 2:10 pm
Hi,
I'm looking for a way to retrieve the column info of a select query (I actually just need the sql data type of its fields). The query can contain joins and unions. I know there are the system tables with schema info, but I don't want to do it table by table. Is there an easy way to get the data type of every item in the query?
BTW, I tried DataAdapter.FillSchema() in .net, but it only gives me the .net data type.
Thanks!
November 2, 2009 at 2:25 pm
You can use this query, which will result in the headers of the columns... But I'm not aware of a possibility to get the SQL datatypes of the columns.
Hope that helps,
set fmtonly on
select * from yourTable
set fmtonly off
Cheers,
J-F
November 2, 2009 at 2:28 pm
Thanks, but that only gives me an empty result set. I need the data type of each column in that result set.
November 2, 2009 at 2:32 pm
There's no real easy way to do this. One way, would be to build out an empty temp table based on the query and then query for the temp table's metadata.
If your base query looked like this:
SELECT t1.Col1,
t2.Col1
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = k2.key
You could do this:
SELECT t1.Col1,
t2.Col1
INTO #tmp
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = k2.key
WHERE 1=2
Querying the system views for #tmp's metadata would give you what you want.
November 2, 2009 at 2:37 pm
Thanks, I thought about that and this is probably the way I'm going to do this. Problem is that column names of two tables that are joined together might be the same generating an duplicate column error on insertion. So I would have to parse my query first and substitute any '*' with the fully qualified column names.
I was just hoping there were an easy solution.
November 2, 2009 at 2:39 pm
does
sp_help tablename
help you??
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply