Hopefully you'll get some informative answers as this is a good question!

When I've done this sort of thing, I used ssms script objects functionality, and in details I check box the stuff I want to generate create scripts for and just really really hoped I didn't miss anything. Doing it yourself could be an interesting project and it would be interesting if this thread generates a complete set of queries. My google foo isn't very good, I'm just glomming onto hopefully a good thread!

These are pretty good:

sp_help <tablename>

sp_helpconstraint <tablename>

here's a possibility for some check constraints:


select chk.definition, *
from sys.check_constraints chk
inner join sys.columns col
 on chk.parent_object_id = col.object_id
inner join sys.tables st
 on chk.parent_object_id = st.object_id


I got that and other info from:

https://stackoverflow.com/questions/14229277/sql-server-2008-get-table-constraints

Sorry for the junky post, I can delete it when the inevitable superior posts start piling in.

(edited for formatting)