I know there are 3rd party tools.
Is there a simple(ish) TSQL script that can be run for one or all table which results in one file per object and takes account of keys, indexes, constrains, defaults and permissions (though the latter is not essential)?
Thanks, but yes, such a DDL extraction IS as simple a thing as running a query - it's called sp_help (which in turns uses sp_helpindexes and sp_helpconstratints). I just didn't want to spend time pulling the parts I want (sp_help is huge) when someone else may have already done it
sp_revtable is not suitable as it just extracts the table def not the indexes, keys, constraints, defaults, etc..
However, I had already been looking at dllgen and it's definitely there! I've written a wrapper to extract all object types, but needs more tweeking..
The sting is that for sub-procs (procs called by other procs who'd previously initialised a temp table), will need somehow the table definition added to the top for the sake of the compiler. It IS a one-off excercise and I WOULD do it manually, but for the fact there are hundreds of such procs