I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free tools (scripts or otherwise) that can do this ?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
This SQL script is a SQL Server 2005 table scripting tool that I wrote to reverse engineer a database schema to SQL. It might do what you want. Load it in SSMS and set 'Results to Text' from the menu. Then run the script. It will reverse engineer most of the major database objects.
There are a number of tutorials available online.
In brief, you will want to create an SMO UrnCollection for all objects in the DB, then pass that UrnCollection to the SMO.Scripter class to script out all the objects.
Not sure if this will work in Express or not, but you could look into SQL Powershell scripting... Check this link out. It's a rough transcript of a SQL Powershell video (the link to that on the page is broken, unfortunately). Specifically, you're looking for the command that looks like this:
gci is Get-ChildItem, | just pipes that output to the next command, % is a short hand for foreach, $_ holds the current item, and we're calling the .script() function.
You should be able to save or copy that info out into a file.
For data scripting, I recommend the SSMS Tools Pack.