How to extract table definition

[Guest]

Hi

I'm attempting to connect to my SQL Server DB get a dataset containing all
tables in the database, then for each table get the columns and access the
column definitions.

The reason I need to do this is to try and automate creation of skeleton
stored procedures for insert, updata and delete. Once I can retrieve the
column defs I can write a basic stored proc using the column names as
parameters, with appropriate data types, lengths etc.

I've created code by using the examples in this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q309488#6 which shows
how to retrieve tables and columns but where I've come unstuck is attempting
to find how I can get the column SQL data type and length.

Any help would be greatly appreciated.

 

[maciek]

Hi Nathan,

If you'll get stuck, try this SQL statement:

select syscolumns.name, systypes.name, syscolumns.length from syscolumns
inner join systypes on systypes.xusertype = syscolumns.xusertype
where syscolumns.id = object_id('categories') order by syscolumns.colid

Hope that helps

Best wishes
Maciek

 

[Sahil Malik]

Nathan,

Did you try SqlDataAdapter.FillSchema?

It's actually fairly simple, check this code out --

DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=Northwind");
SqlDataAdapter da = new SqlDataAdapter("SELECT CustomerID, CompanyName,
ContactName FROM Customers", conn);
da.FillSchema(ds, SchemaType.Source);

If you do a ds.WriteXmlSchema --- You should get ---

<?xml version="1.0" standalone="yes" ?>
- <xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true">
- <xs:complexType>
- <xs:choice maxOccurs="unbounded">
- <xs:element name="Table">
- <xs:complexType>
- <xs:sequence>
- <xs:element name="CustomerID">
- <xs:simpleType>
- <xs:restriction base="xs:string">
<xs:maxLength value="5" />
</xs:restriction>
</xs:simpleType>
</xs:element>
- <xs:element name="CompanyName">
- <xs:simpleType>
- <xs:restriction base="xs:string">
<xs:maxLength value="40" />
</xs:restriction>
</xs:simpleType>
</xs:element>
- <xs:element name="ContactName" minOccurs="0">
- <xs:simpleType>
- <xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
- <xs:unique name="Constraint1" msdatarimaryKey="true">
<xs:selector xpath=".//Table" />
<xs:field xpath="CustomerID" />
</xs:unique>
</xs:element>
</xs:schema>

... and the above obviously has the proper MaxLength values alongwith other
stuff. .. now .. don't use this in a production app because it executes a
fairly heavy query to get the above information.
Hope that helped !!

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik