How to retrieve schema bound to a Query?

Hi ,
Can you please tell me how to retrieve schema associated with a query? Here
is the sample query for which i want to know DATATYPE, LENGTH of every colum
n
which i'm selecting in the query.
SELECT Tbl2.Name, Tbl3.Age, Tbl1.ID, Ttb2.DOB
FROM Table1 Tbl1,
Table2 Tbl2
WHERE Tbl1.Key = Tbl2.Table1Key
Thanks in advance for spending your valuable time to look in to this
problem. I would be very much thankfull to you if you help me to write SQL
script( or C# code) to get the schema bound with the query.
Regards,
Gopinath MGopi Try this
select * from information_schema.columns where table_Name = Table1 and
COLUMN_NAME IN([Name], Age, [ID], DOB)
Regards
R.D
"Gopinath M" wrote:

> Hi ,
> Can you please tell me how to retrieve schema associated with a query? Her
e
> is the sample query for which i want to know DATATYPE, LENGTH of every col
umn
> which i'm selecting in the query.
> SELECT Tbl2.Name, Tbl3.Age, Tbl1.ID, Ttb2.DOB
> FROM Table1 Tbl1,
> Table2 Tbl2
> WHERE Tbl1.Key = Tbl2.Table1Key
>
> Thanks in advance for spending your valuable time to look in to this
> problem. I would be very much thankfull to you if you help me to write SQL
> script( or C# code) to get the schema bound with the query.
> Regards,
> Gopinath M
>
>|||Hi R.D,
Thank you very much for spending your valuable time. The script you
have given will work if i know the table and the column for which i want to
retrive schema well in advance. But my requiremet is "a user inputs a query
and i want to find out schema associated with that query".
Users are allowed to key in a valid sql select statement. For this
select statement i want to retrieve schema. Also, i dont have access to
create(any DDL) any type of temporary table in the production environment fo
r
retrieving schema. I'm struck up with the problem for a couple days. Please
help me in solving this.
Regards,
Gopinath M
"R.D" wrote:
> Gopi Try this
> select * from information_schema.columns where table_Name = Table1 and
> COLUMN_NAME IN([Name], Age, [ID], DOB)
> Regards
> R.D
> "Gopinath M" wrote:
>|||gopi
you got to query VIEW : information_schema.columns
where all properties of columns are listed.
Regards
R.D
"Gopinath M" wrote:
> Hi R.D,
> Thank you very much for spending your valuable time. The script you
> have given will work if i know the table and the column for which i want t
o
> retrive schema well in advance. But my requiremet is "a user inputs a que
ry
> and i want to find out schema associated with that query".
> Users are allowed to key in a valid sql select statement. For this
> select statement i want to retrieve schema. Also, i dont have access to
> create(any DDL) any type of temporary table in the production environment
for
> retrieving schema. I'm struck up with the problem for a couple days. Pleas
e
> help me in solving this.
> Regards,
> Gopinath M
>
> "R.D" wrote:
>|||R.D,
Is there any logic or script which will identify the table name of every
column we listed in the select statment?
If i'm able to idenfity the table name associated with a column say tbl1.ID
then i can query the system views to get the information for datatype of
column ID in the table Table1( as aliased tbl1).
Also, when i get an expression in the select statement like
SELECT (2 * tbl1.Id) FROM XYXTABLE tbl
then i need a logic to find the type of data the query is giong to return.
i've a solution like this... if i create a view on the database with the
user select statement i can use Sysobject and syscolumns table to retrive
column datatypes and length. But as our applications are going to run on
highly secured production environment, i dont have rights to create view or
temporary tables.
"R.D" wrote:
> gopi
> you got to query VIEW : information_schema.columns
> where all properties of columns are listed.
> Regards
> R.D
> "Gopinath M" wrote:
>|||Gopi
can you join Information.schema.columns with your query using tablename.col
= sysview.col.
That is possible. You may try
Regards
R.D
"Gopinath M" wrote:
> R.D,
> Is there any logic or script which will identify the table name of every
> column we listed in the select statment?
> If i'm able to idenfity the table name associated with a column say tbl1.I
D
> then i can query the system views to get the information for datatype of
> column ID in the table Table1( as aliased tbl1).
> Also, when i get an expression in the select statement like
> SELECT (2 * tbl1.Id) FROM XYXTABLE tbl
> then i need a logic to find the type of data the query is giong to return.
> i've a solution like this... if i create a view on the database with the
> user select statement i can use Sysobject and syscolumns table to retrive
> column datatypes and length. But as our applications are going to run on
> highly secured production environment, i dont have rights to create view o
r
> temporary tables.
>
> "R.D" wrote:
>