1

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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 10:59

1 Answer 1

3

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.

3
  • 2
    Just for clarity this only talks about column names and types (and leaves out things like nullability, default constraints, check constraints, precision/scale, primary keys, indexes, etc., never mind all of the other objects). It's going to take quite a bit of extension to turn this into something that generates "a complete representation of the schema." Jan 16, 2014 at 17:13
  • 1
    Much less anything that in the most remote of real-world cases could be defined as "simple."
    – Avarkx
    Jan 16, 2014 at 17:20
  • "there is no click - click - finish and export to xml" - why do you assume I search for one? I was in search for a script exporting to XML/similar (title was misleading, fixed that). Similar to sp_help. The metadata is "in there", I just need a script, perf is not important, it's done on builtime as a validation step on the CI system. All I'm looking for is a script that dumps all relevant infos, I can translate that myself, I've done such things a lot in the past. If Redgate had something like that I would be happy to use that, but afaik they only export to SQL scripts. Guess its SMO again..
    – hko
    Jan 16, 2014 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.