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

how to retrieve datas from a SqlDataSource

Hi! I'm a novice in asp .net

I've a SqlDataSource component on an Aspx page.
In the associated C# file, I would like to use datas from the query stored in the SqlDataSource component.

How to do this?

Thanxs :)

An example

<%@.PageLanguage="C#" %>

<%@.ImportNamespace="System.Data" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

protected void Page_Load(object sender, EventArgs e)

{

DataView dv = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());

DataTable dt = dv.Table;

// display table

bool pagesToDo = true;

int index = 0;

while (pagesToDo)

{

Table t = GetDisplayTable(dt);

t.CssClass = "PageBreakStyle";

Controls.Add(t);

int nextPageEndsAt = index + 10;

while (index < nextPageEndsAt && pagesToDo)

{

TableRow tr = new TableRow();

t.Rows.Add(tr);

foreach (DataColumn dc in dt.Columns)

{

TableCell tc = new TableCell();

tr.Cells.Add(tc);

tc.Text = dt.Rows[index][dc.ColumnName].ToString();

}

index++;

if (index == dt.Rows.Count)

{

pagesToDo = false;

break;

}

}

}

}

Table GetDisplayTable(DataTable dt)

{

Table t =newTable();

TableHeaderRow thr =newTableHeaderRow();

t.Rows.Add(thr);

foreach (DataColumn dcin dt.Columns)

{

TableHeaderCell thc =newTableHeaderCell();

thc.Text = dc.ColumnName;

thr.Cells.Add(thc);

}

return t;

}

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

<styletype="text/css">

.PageBreakStyle

{

page-break-after:always;

}

</style>

</head>

<body>

<formid="form1"runat="server">

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT [ProductID], [ProductName] FROM [Products]"></asp:SqlDataSource>

</form>

</body>

</html>

|||

Thanks, it works!

very cool man ^^