How to script SQL Server database objects

Sometimes you may need to script SQL objects using T-Sql instead of rely on SSMS or thirdy part tools.


SQL Server 2005 and 2008 offer you the chance to query their system views to obtain what you need.


Note that you could use a dll from SQL Server SDK, but here we are trying the sql approach.


For objects like procedures, triggers, functions and views you may use the OBJECT_DEFINITION function and something like


SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.MyStoredProcedure'))

but combining the properties coming from sys.objects and sys.sql_modules views we will extract all the data we need.

SELECT
CASE

  WHEN O.type = 'V' THEN 'View'

  WHEN O.type = 'P' THEN 'Procedure'

  WHEN O.type = 'TR' THEN 'Trigger'

  WHEN O.type IN ('FN','IF','TF') THEN 'Function'

 END [type],

 SCHEMA_NAME(O.schema_id) obj_schema,

 O.name obj_name,

 CONVERT(VARCHAR, O.modify_date, 120) modify_date,

 SM.definition code

FROM

 sys.objects O

  INNER JOIN

 sys.sql_modules SM ON

  SM.object_id = O.object_id

WHERE

 O.is_ms_shipped = 0 AND

 O.type IN ('P','V','FN','IF','TF','TR')

ORDER BY

 type,

 obj_schema,

 obj_name

Table property definitions are spread across many system views. The most important are sys.tables and sys.columns.


SELECT
SCHEMA_NAME(TB.schema_id) tb_schema,

 TB.name tb_name,

 CL.name cl_name

FROM

 sys.tables TB

  INNER JOIN

 sys.columns CL ON

  CL.object_id = TB.object_id

ORDER BY

 tb_schema,

 tb_name,

 CL.column_id

I don't want to bother you with all the details, so you can download the full script.


ScriptSqlObjects.zip


It allows to generete the code for tables, views, procedures, triggers, functions and synonyms.