question about how to access column/field information

Hi,
I am just starting out in SQL Server and, as always, I am throwing myself in
at the deep end by trying more advanced things. I understand the basics like
INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass some kind
of SQL object to a function which will then extract the field names, field
types, and field sizes from that object.
I have tried using DataSet and DataTable but I cannot see how I can extract
all three things I need from the column information. I'd also rather do what
I am doing on the actual table structure itself, rather than on a returned
set of records from the table (dataset).
Any help would be appreciated.
Thanks in advance,
RichardAs far as you are writing in the SQL Server group I think that you want to
get this information via TSQL ?!
If so, just look at this Query where #Orders can be replaced with the Name
of your Object to be queried. The Best way woud be to solve the Object name
to a ObjectID and to search for then, just for easy understanding it is
filtered via the object name here. :-)
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
That will give you all the information youll wanted. If you want the
information via C# oder ADO.NET in common you could get i with FILLSCHEMA:
http://msdn.microsoft.com/library/d...r />
atopic.asp
HTH, Jens Smeyer.
"Richard" <acorn@.acorn.net> schrieb im Newsbeitrag
news:425cb509$1_3@.news.melbourne.pipenetworks.com...
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing myself
> in at the deep end by trying more advanced things. I understand the basics
> like INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass
> some kind of SQL object to a function which will then extract the field
> names, field types, and field sizes from that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself, rather
> than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
>|||Richard wrote:
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing
> myself in at the deep end by trying more advanced things. I
> understand the basics like INSERT, DELETE, UPDATE, SELECT etc What I
> am trying to do is pass some kind of SQL object to a function which
> will then extract the field names, field types, and field sizes from
> that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself,
> rather than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
You can interrogate the system tables or ideally use the
INFORMATION_SCHEMA views. See BOL for more information.
I'm not sure what you mean by "pass a SQL object to a function", unless
what you're saying is that you want a stored procedure that returns some
meta data for a table.
If you're executing a query and want the result set details, you can do
that easily from client in ADO or ADO.Net.
Can you provide some additional detail about what you're trying to do?
David Gugick
Imceda Software
www.imceda.com|||What I'd like to be able to do is, given the name of a table or a table
object of some description, interrogate that table for all the fields in
that table, and also information about those fields, so that I can process
that information.
As much information as possible about the fields would be nice, but at the
minimum I need the field name, type (char, bigint etc) and size of the
field.
Thanks again,
Richard
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> You can interrogate the system tables or ideally use the
> INFORMATION_SCHEMA views. See BOL for more information.
> I'm not sure what you mean by "pass a SQL object to a function", unless
> what you're saying is that you want a stored procedure that returns some
> meta data for a table.
> If you're executing a query and want the result set details, you can do
> that easily from client in ADO or ADO.Net.
> Can you provide some additional detail about what you're trying to do?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Richard
Read up this article
http://www.sommarskog.se/dynamic_sql.html
"Richard" <acorn@.acorn.net> wrote in message
news:425cc012_1@.news.melbourne.pipenetworks.com...
> What I'd like to be able to do is, given the name of a table or a table
> object of some description, interrogate that table for all the fields in
> that table, and also information about those fields, so that I can process
> that information.
> As much information as possible about the fields would be nice, but at the
> minimum I need the field name, type (char, bigint etc) and size of the
> field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>|||Use the INFORMATION_SCHEMA views as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard" <acorn@.acorn.net> wrote in message news:425cc012_1@.news.melbourne.pipenetworks.co
m...
> What I'd like to be able to do is, given the name of a table or a table ob
ject of some
> description, interrogate that table for all the fields in that table, and
also information about
> those fields, so that I can process that information.
> As much information as possible about the fields would be nice, but at the
minimum I need the
> field name, type (char, bigint etc) and size of the field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>