Sql Server: how to validate schema against a textual representation (XML or the like) natively?

Question

I need a simple "complete dump" of the schema for our repo, in human-readable format: Sprocs, Views, the like. We need to "fix" some old schema issues that cropped in over the time, and "clean up" the schema. In addition, constant merging between quite different branches should be as easy as possible, therefor a complete representation of the schema to begin with, and not migration scripts, as they are mergeunfriendly (Schema conflicts would have to be found be tests, unfortunately our test coverage is a joke to begin with).

Just as a reminder: any binary blop or .bak file are not the goal, as well as any GUI tools. This should run on a CI server. We need full automation for the schema vialidation from the commandline, and a humanreadable filfeformat for the schemareprenstation in the repository.

I know that Sql Server itself allows to export the schema of a table (maybe a whole DB) as XML, but I can't find the statement.. any help?

Or any (preferably c#/.NET related, preferably opensource) tools that can do such a thing?

Sql Server Version would be 2008 and up.

Edit: I'm speaking of a script, not some GUI functionality.. I've seen that done by a colleague, and can't ask him as he's on vacation for quite some time.. searched for a while, but didn't find the script

Edit2 I need a schema compare and diff like Tools like Redgate provide, just for the commandline, and because of our dev focused process we need a humanreadable format.. XML or whatever. One approach is showed by http://schemacrawler.sourceforge.net -> http://schemacrawler.sourceforge.net/snapshot-examples/snapshot.txt

I've written a C# tool to migrate a Schema from Sql Server to e.g. LocalDb via SMO , so I know the challenges, but I was hoping that this time I could save the time, because things like sp_help and XML schema support is built right into Sql server. So the question is more like "is there a script which could export all at once". This is for built time, not production time, so perf doesn't matter at all.

Answer 1

As Aaron mentioned, there is no click - click - finish and export to xml option in sql server.

Rather you can use FOR XML AUTO, XMLDATA to do that using T-SQL.

use databaseName
go
SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
        t.name AS table_name,
        c.name AS column_name,
        st.name as data_type,
        c.max_length 
        ,c.Is_ANSI_Padded
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.types st on c.system_type_id = st.system_type_id
where t.is_ms_shipped = 0 and t.name <> 'sysdiagrams'
order by t.name
FOR XML AUTO, XMLDATA

Refer to : Specifying the XMLDATA Schema Option in a Query

Generating an XML-Data schema is an overhead on the server, is likely to affect performance, and should be used only when data types are needed.

Alternatively, if you just want to script out the schema, then refer to this answer for more details.