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.
T-sql – How to get an object’s “script as…” script through code
ssmst-sql
Related Question
- Sql-server – Auto-generate scripts to create tables, indexes, etc
- Sql-server – Cannot Start/Stop Services using SSMS 2014 (but 2008R2 works)
- Sql-server – Opening a stored procedure file multiple times in sql server management studio
- Sql-server – Connect to Database Engine Prompt on Script Object
- Sql-server – My SSMS is bringing wrong object in “Script Stored Procedure as” > “CREATE To” > “New Query Editor Window”
Best Answer
Exactly which combination of tables to refer to depends on the object type, but it is all in the tables in the
sysschema of each database. For instancesys.tablescontains user table information,sys.columnslists 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_idfrom which you can lookup the name insys.objects.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.