How to get the XML schema of the database structure

Question

SQL

Problem following: there is a DB (sql server 2005) about 300 tables with the data. You need to get a schema that describes the structure of this database in XML format.
I heard about XML/SQL, but found only this way:
SELECT TOP 0 * from Table_List for xml auto, xmldata
But I'm afraid that "multiplication" of tables will be performed first, which will kill the server.
Tell me where to dig.

Answer 1

You are on the right track - try the code below. Server performance will not be affected.
select sch.name as schema_name,tab.name as table_name,col.name as column_name,
systyp.name as type_name, typ.is_user_defined, typ.name as user_type,
col.max_length, col.precision, col.scale, col.collation_name, col.is_nullable as nullable,
col.is_identity,col.is_computed,cc.definition as computedExpression, col.is_rowguidcol,
def.definition as defaultValue, cast(ep.value as varchar(7500)) as comment
from sys.columns as col
inner join sys.tables as tab on col.object_id=tab.object_id
inner join sys.schemas as sch on tab.schema_id = sch.schema_id
inner join sys.types as typ on col.user_type_id= typ.user_type_id
inner join sys.types as systyp on col.system_type_id = systyp.system_type_id
and systyp.is_user_defined=0 and systyp.user_type_id = systyp.system_type_id
left join sys.default_constraints as def on col.default_object_id = def.object_id
left join sys.extended_properties as ep on ep.major_id=tab.object_id
and ep.minor_id=col.column_id and ep.class='1' and ep.class_desc='OBJECT_OR_COLUMN'
and ep.name='MS_Description'
left join sys.computed_columns as cc on tab.object_id=cc.object_id and col.column_id = cc.column_id
FOR XML PATH('column'), ROOT('datadictionary')--, ELEMENT