Retrieve a Table Schema Only, in XML Format

This week I had an interesting question: how to retrieve the structure of a table in XML format.

SQL Server, or to be more exact T-SQL, has provided support for XML since the 2000 version.  The syntax is very simple, just add FOR XML to any SELECT query and you’re done.  Actually you will probably have to add other parts to the clause in order to have the output meet your requirements.

The question was how to retrieve the schema.  This can be achieved by adding “FOR XML AUTO, XMLDATA” when querying SQL Server 2000 databases or “FOR XML AUTO, XMLSCHEMA” when querying SQL Server 2005 and later versions – the XMLDATA clause has been deprecated.  So our query will be similar to:

SELECT ...
FROM ...
FOR XML AUTO, XMLSCHEMA

The next step is how remove the data from the resultsand return only the schema.  This can be achieved by adding the TOP clause to the SELECT statement as shown below:

-- SQL Server 2000
SELECT TOP 0 ...
FROM ...
FOR XML AUTO, XMLSCHEMA

And for the more recent versions:

-- SQL Server 2005 and later
SELECT TOP (0) ...
FROM ...
FOR XML AUTO, XMLSCHEMA

Limiting the results to zero rows returns only the data types.  An interesting trick!