Get the column name of dynamical SQL?

I need to execute a lot of dynamical SQL (select only). These select
statement have different column number and name.
Any easy way to get the column names of a select statement string?How did your dynamic SQL get the column names in the first place?
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||What I want to implement is:
A function with parameter of SQL statement string,
return the column names.
I am trying to avoid parsing the string. I guess SQL server may have some
internal stored procedure to get these information.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How did your dynamic SQL get the column names in the first place?
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||not so easily. you could:
1. dump the result into a temp table and look up its definition -
e.g.
exec ('select top 0 * into mytmp from sysobjects')
select column_name
from information_schema.columns
where table_name='mytmp'
2. extract the stuff between "select" and "from".
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||yes, both way are cumbersome.
or any easy way to get the number of columns?
"oj" wrote:

> not so easily. you could:
> 1. dump the result into a temp table and look up its definition -
> e.g.
> exec ('select top 0 * into mytmp from sysobjects')
> select column_name
> from information_schema.columns
> where table_name='mytmp'
> 2. extract the stuff between "select" and "from".
> --
> -oj
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||nick (nick@.discussions.microsoft.com) writes:
> What I want to implement is:
> A function with parameter of SQL statement string,
> return the column names.
> I am trying to avoid parsing the string. I guess SQL server may have some
> internal stored procedure to get these information.
So what is your real business problem? This sort of thing is somewhat
easy to do from a client program, but not from within SQL itself. Which
is not so strange. This sort of information is not so interesting to
the server-side which delivers data. It is of course interesting on
the client-side, as the client needs to be able to investigate what data
it's getting from the server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||if the columns are exploded (i.e. separated by a comma and no * used), you
can count the number of commas.
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBB38F4E-4C5D-4D54-BF58-9CAEF8F7966A@.microsoft.com...
> yes, both way are cumbersome.
> or any easy way to get the number of columns?
> "oj" wrote:
>