Help developing a SQL Query tool

[Donnie]

Hello All

I am trying to find some information on how to design/develop a SQL Query tool embedded in an ASP.NET web application
What I would like to have is a tool that helps a user write SQL statements that are in turn used to query a database. The results of the query will then be displayed in a DataGrid. I'd like the tool to help the user write the query (similar to a Query By Example tool). I currently have a web app that allows a user to directly enter a SQL statement in text box and then the user can click a button and execute that query and display the results in a DataGrid (users are restricted to select statements only, no UPDATE, INSERT, DELETE, etc).

I'm sure someone has already (or tried to develop) developed a tool like this to embed in a web application. I've searched
the Internet for while now and haven't found any good examples yet. Does anyone have any ideas/suggestions on wher
to find info on developing a tool like this

Thank
Donnie

[Felbrigg]

Try this query, it may help you populate some of the tools controls. It
returns a limited subset the databses structure

SELECT sysobj.name AS 'Table',syscol.name AS 'Column',CASE WHEN syscol.name
IN ( index_col(object_name(sysobj.id), sysind.indid,
1),index_col(object_name(sysobj.id), sysind.indid,
2),index_col(object_name(sysobj.id), sysind.indid,
3),index_col(object_name(sysobj.id), sysind.indid, 4) )THEN 1 ELSE 0 END AS
'PrimaryKey',systypes.name AS 'Datatype',syscol.length AS
'Length',syscol.prec AS 'Precision',ISNULL(syscol.scale,0) AS 'Scale',
syscol.isnullable AS 'Nullable', object_name(FKeys.rKeyid) AS
'ForeignTable',col_name(FKeys.rKeyid, FKeys.rkey) AS 'ForeignColumn',(SELECT
col_name(FKeys.rKeyid, min(colid)) FROM syscolumns sysDescCol Where
sysDescCol.id = FKeys.rKeyid AND sysDescCol.xtype IN (167,175)) AS
'ForeignDescription',(SELECT col_length
(object_name(FKeys.rKeyid),col_name(FKeys.rKeyid, min(colid))) FROM
syscolumns sysDescCol Where sysDescCol.id = FKeys.rKeyid AND
sysDescCol.xtype IN (167,175)) AS 'DescriptionLength' FROM sysobjects sysobj
JOIN syscolumns syscol ON sysobj.id = syscol.id Join systypes ON
syscol.xtype = systypes.xtype AND systypes.xtype = systypes.xusertype JOIN
sysindexes sysind ON sysobj.id = sysind.id AND (sysind.status & 2048) <> 0
LEFT JOIN sysforeignkeys FKeys ON sysobj.id = FKeys.fkeyid AND syscol.colid
= FKeys.fkey WHERE sysobj.type = 'U' AND sysobj.name <> 'dtProperties' ORDER
BY 1, 3 DESC, 2

"Donnie" <an*******@discussions.microsoft.com> wrote in message
news:27**********************************@microsof t.com...

Hello All-

I am trying to find some information on how to design/develop a SQL Query tool embedded in an ASP.NET web application. What I would like to have is a tool that helps a user write SQL statements that are in turn used to query a database. The results of the query will
then be displayed in a DataGrid. I'd like the tool to help the user write
the query (similar to a Query By Example tool). I currently have a web app
that allows a user to directly enter a SQL statement in text box and then
the user can click a button and execute that query and display the results
in a DataGrid (users are restricted to select statements only, no UPDATE,
INSERT, DELETE, etc).
I'm sure someone has already (or tried to develop) developed a tool like this to embed in a web application. I've searched the Internet for while now and haven't found any good examples yet. Does anyone have any ideas/suggestions on where to find info on developing a tool like this?

Thanks
Donnie

[Lloyd Sheen]

Not sure what this is supposed to bring but for me it just brings error
messages.

Lloyd Sheen

"Felbrigg" <so*****@microsoft.com> wrote in message
news:eu****************@TK2MSFTNGP10.phx.gbl...

Try this query, it may help you populate some of the tools controls. It
returns a limited subset the databses structure

SELECT sysobj.name AS 'Table',syscol.name AS 'Column',CASE WHEN syscol.name IN ( index_col(object_name(sysobj.id), sysind.indid,
1),index_col(object_name(sysobj.id), sysind.indid,
2),index_col(object_name(sysobj.id), sysind.indid,
3),index_col(object_name(sysobj.id), sysind.indid, 4) )THEN 1 ELSE 0 END AS 'PrimaryKey',systypes.name AS 'Datatype',syscol.length AS
'Length',syscol.prec AS 'Precision',ISNULL(syscol.scale,0) AS 'Scale',
syscol.isnullable AS 'Nullable', object_name(FKeys.rKeyid) AS
'ForeignTable',col_name(FKeys.rKeyid, FKeys.rkey) AS 'ForeignColumn',(SELECT col_name(FKeys.rKeyid, min(colid)) FROM syscolumns sysDescCol Where
sysDescCol.id = FKeys.rKeyid AND sysDescCol.xtype IN (167,175)) AS
'ForeignDescription',(SELECT col_length
(object_name(FKeys.rKeyid),col_name(FKeys.rKeyid, min(colid))) FROM
syscolumns sysDescCol Where sysDescCol.id = FKeys.rKeyid AND
sysDescCol.xtype IN (167,175)) AS 'DescriptionLength' FROM sysobjects sysobj JOIN syscolumns syscol ON sysobj.id = syscol.id Join systypes ON
syscol.xtype = systypes.xtype AND systypes.xtype = systypes.xusertype JOIN
sysindexes sysind ON sysobj.id = sysind.id AND (sysind.status & 2048) <> 0
LEFT JOIN sysforeignkeys FKeys ON sysobj.id = FKeys.fkeyid AND syscol.colid = FKeys.fkey WHERE sysobj.type = 'U' AND sysobj.name <> 'dtProperties' ORDER BY 1, 3 DESC, 2

"Donnie" <an*******@discussions.microsoft.com> wrote in message
news:27**********************************@microsof t.com...

Hello All-

I am trying to find some information on how to design/develop a SQL

Query tool embedded in an ASP.NET web application.

What I would like to have is a tool that helps a user write SQL

statements that are in turn used to query a database. The results of the query will
then be displayed in a DataGrid. I'd like the tool to help the user write
the query (similar to a Query By Example tool). I currently have a web app that allows a user to directly enter a SQL statement in text box and then
the user can click a button and execute that query and display the results
in a DataGrid (users are restricted to select statements only, no UPDATE,
INSERT, DELETE, etc).

I'm sure someone has already (or tried to develop) developed a tool like this to embed in a web application. I've searched

the Internet for while now and haven't found any good examples yet.

Does anyone have any ideas/suggestions on where

to find info on developing a tool like this?

Thanks
Donnie