Hi,

I'm using MS SQL Server 2005, and I'm currently working on writing a
export and import scheme for my databases.

Our goal is to move completely away from MS's SQL Management Studio,
and use our own database management utility that does things in a a
customized and limited way. Among the various tasks this utility must
include is the ability to export a table to XML and then import a table
from XML.

I'm writing the utility in C#, and initially I tried just using the
methods .ReadXML() and .WriteXML() on a DataSet object. It can then
easily be traded back and forth.

However, for importing, this only works if the table is already in
existence. The XML schema definition created by the DataSet object
doesn't contain enough information about the table definitions to
recreate the table accurately. For example, it uses a single datatype
"string" to represent all char, varchar, nvarchar, text, and ntext
types. It also doesn't include constraints.

I would like to extract that information from the SQL server at the
time of export and include it in my exported XML schema. I have been
able to the get the table datatype definitions by querying the view
INFORMATION_SCHEMA.COLUMNS, eg:

USE MyCatalog;
GO
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
COLUMN_DEFAULT,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

So, that's enough information generate a new CREATE TABLE command, if i
don't include any unique, primary key, indentity, or other
constraints....

Unfortunately, that's not a very good copy of the table, if it's
missing all that information. Any idea how I can get the constraint
definitions? I found a view that will tell me the unqiue object names
of the constraints that are active on a table, however it doesn't
actually tell me what the definition of those constraints are..

Also, while I'm asking.. Is there an easier way to do this? My goal is
to end up with a CREATE TABLE statement that I can package along with
my XML, or at least enough data to generate a CREATE TABLE statement
when I import it. From the user interface of MS SQL Management Studio
you can right click on a table and select Script Table As -> CREATE to
-> ... and generate a CREATE script. Is there a SQL query that will do
something like that and just spit out a CREATE script for whatever
table I point it at?

Thanks,
Troy