In SSMS I can right click on an object in Object Explorer (e.g. a table, key, constraint, or index) and go Script As -> Create To -> New Query Editor Window. This outputs the creation script in t-sql for that object. I would like to know if there is a function or procedure available that can do that which I can call, as I'm writing a script and can't rely on UI elements.
Exactly which combination of tables to refer to depends on the object type, but it is all in the tables in the
sys schema of each database. For instance
sys.tables contains user table information,
sys.columns lists columns in tables and input parameters to rpocedures, sys.all_sql_modiles includes the definitions of stored procedures and views, and so on.
Most are keyed on the
object_id from which you can lookup the name in
For instance to list the definition of SomeProcedure you might run
SELECT sm.[definition] FROM sys.objects so JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id WHERE so.name = 'SomeProcedure'.
See https://msdn.microsoft.com/en-us/library/ms189783.aspx for reference information.
The system procedures sp_help and sp_helptext can be used to do the legwork in many cases where you need the information for manual inspection, but if you are trying to take programmed action based on the information it is usually better to refer to the data in
sys.* directly than try to parse the output of these procedures.
You can use SQL Server Management Objects (SMO). They are a set of classes and APIs for interaction with almost all aspects of SQL Server. There is support for scripting. The SMO dlls can be invoked from any of the usual programming languages, Powershell or even, if you're desperate enough, from within T-SQL.