Quickduck

Have you ever wanted to script all the db objects in your database and customize how you format the scripts?

So have I!

Ideally, I wanted to script all the db objects (eg. procs, udfs, grants, defaults, permissions, tables, etc) and have each object output into a separate file and formatted a particular way.

So I’ve created a couple of sprocs that allow you to do this.

Usage:
EXEC dbo.CreateSqlScripts
‘<directory>’, ‘<sqlServerInstance>’, ‘<dbname>’, ‘<object type or null>’, ‘<formattingoptions>’

Example:
EXEC dbo.CreateSqlScripts
‘c:\temp\sql\’, ‘localhost\SQLLOCALMACHINE’, ‘MyDB’, NULL, 73879 — all db objects

Example:
EXEC dbo.CreateSqlScripts
‘c:\temp\sql\’, ‘localhost\SQLLOCALMACHINE’, ‘MyDB’, ‘P’, 73879 — stored procedures only

A bonus feature of having your db scripted out is that you can use a 3rd party program like DBGhost to generate a new db from the scripts directory.

This is really handy if you want to have your db source controlled.