3 ms·
> the alternative is to maintain a "drop-all.sql" file If you really want to drop a whole schema (i.e. all its constituant objects) and recreate, then you coul
by dspillett 10y ago
> the alternative is to maintain a "drop-all.sql" file
If you really want to drop a whole schema (i.e. all its constituant objects) and recreate, then you could write a procedure to do so by cursoring through sys.objects (joined to other system tables as needed, sys.schemas to filter by name rather than schema ID for a start) and issuing "DROP <OBJECT> <name>" for each via "EXEC (@VarContainingDropStatement)". Having a built-in might be nice, but writing your own reusable procedure (rather than a more hard-coded script) should not be difficult.
> tedious and error-prone when making use of foreign-key relationships
If following the "cursor through sys.objects" approach that can be managed by making sure FK constraints are dropped first, either by forcing the objects listed out of the cursor into a particular order or by a separate loop first.
> T-SQL has MERGE, which is vastly more complicated and verbose.
I'm with you there. It also has some odd locking issues that you need to be aware of before they bite your rear.
I'd agree generally that T-SQL has some room for improvement. Date and string manipulation functions are sometimes lacking I find, though as I use little other than SQL Server I can't comment on other environments being better/worse. Being able to easily define a new aggregate procedure as I believe you can in postgres is something I could have made good use of in the past.