Get object script in SQL Server via T-SQL?

Question

I need to get the creation script of an object in a SQL Server database. I need the exact same script that was used to create it. Like when you use SSMS to alter a view you already defined, and then the script is there, just the way you've defined it earlier.

I need to do this in T-SQL. Where does SQL Server store the scripts related to the database objects? I can't find the related view in system catalog views.

What I'm trying to find is something like:

select creation_script -- I know this column doesn't exist
from sys.objects
where name = 'CustomersView'

Answer 1

This is not feasible in T-SQL. Not all object have a definition stored and you would have to reverse engineer CREATE scripts for basic things like tables and indexes.

Rather than reinvent the wheel (in T-SQL), do the proper thing and reuse the component dedicated for this purpose the SMO Scripter class. Is already available, works, is up to date with all the latest SQL Server objects, is free to redistribute along with SMO. Simply move your scripting logic out of T-SQL.

Answer 2

While not disputing that the most comprehensive method is to use SMO as Remus suggested in his answer, this question does request to get the info via T-SQL and that info does exist for some objects. Just keep in mind that this info:

• covers a small set of object types
• does not include any GRANT / DENY statements. But you can get that info from sys.database_permissions and sys.database_principals.
• does not include any IF EXISTS DROP logic
• does not include the session settings of ANSI_NULLS and QUOTED_IDENTIFIER. Those you get from sys.sql_modules via the [uses_ansi_nulls] and [uses_quoted_identifier] fields.
• might not include the Schema Name. If the Schema Name was not explicitly stated in the CREATE (or ALTER) statement, then the Schema Name will not be a part of the definition. The Schema will have been whatever the default Schema was for the User that created the object, at the time of creation. If the Schema Name is not included in the definition and cannot be assumed to be something like dbo, then it will have to be discovered via OBJECT_SCHEMA_NAME([object_id])

---

Object Catalog Views

The sys.sql_modules Catalog View has the full CREATE... statement for the following objects (anything encrypted will return NULL for the [definition]):

• SQL Stored Procedure
• Replication-filter-procedure
• View
• SQL DML trigger
• SQL Database-level DDL trigger
• SQL scalar function
• SQL inline table-valued function
• SQL table-valued-function
• Rule (old-style, stand-alone)
• Default (stand-alone, NOT constraint)

SELECT OBJECT_SCHEMA_NAME([object_id]) AS [SchemaName],
       OBJECT_NAME([object_id]) AS [ObjectName],
       *
FROM   sys.sql_modules;

The sys.check_constraints and sys.default_constraints Catalog Views will return the logic portion of their respective constraints as the [definition]:

SELECT *
FROM   sys.check_constraints;

SELECT *
FROM   sys.default_constraints;

Metadata Function

The OBJECT_DEFINITION function will return the same data that is in the [definition] field of the three previously mentioned Catalog Views, based on the specified [object_id]:

SELECT so.name,
       so.[type],
       so.type_desc,
       OBJECT_DEFINITION(so.[object_id]) AS [Definiton]
FROM   sys.objects so
WHERE  OBJECT_DEFINITION(so.[object_id]) IS NOT NULL;

---

Other Objects

Here are some resources for finding the meta-data related to all object types:

• Object Catalog Views
• Metadata Functions
• Querying the SQL Server System Catalog FAQ

Answer 3

Use Profiler for a moment (or Extended Events or a server-side trace), and then produce some of the scripts you want via Management Studio. Make sure you've got the scripting options you want turned on - you may prefer to use Tasks | Generate Scripts at the database level.

Anyway, the point being that you can have a look at what queries run, and get a feel for what's involved.

Another option is to start with a fresh database, and use DDL triggers to store everything that gets run to create or alter objects. Then you have the actual scripts used and you have a repository you can easily hook into when required, with the exact queries that were used at the time.

Answer 4

Several years back I was able to generate object scripts on-the-fly using Distributed Management Objects and sp_OACreate. If your SQL instance is sufficiently old you will be able to use these. It was surprisingly easy and operationally reliable.

Newer versions of SQL Server have SMO which can be called from .Net.