4

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'
  • 1
    Why the exact same script? What version of SQL server? – Mark Sinkinson Mar 14 '15 at 7:41
  • 1
    If you're interested in views and procedures, you could use sp_helptext – James Z Mar 14 '15 at 8:12
  • @MarkSinkinson, by exact, I don't mean even capitalization. I just want all the structures to be the same. All with options, all cascades on foreign keys, etc. – Saeed Neamati Mar 14 '15 at 9:42
  • 1
    Ah. That kind of script isn't stored anywhere. You'd have to piece it together yourself from all the different sys tables – Mark Sinkinson Mar 14 '15 at 9:45
  • 1
    For the rest I think you'll have to parse them together from the DMVs. – James Z Mar 14 '15 at 9:52
6

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.

4

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:

2

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.

  • +1. Your second option (DDL triggers) is very interesting. Can you guide me more in that? How about updates? How to get the latest definition of objects? How to remove older definitions? – Saeed Neamati Mar 15 '15 at 6:14
  • If you store every CREATE, ALTER, DROP, then you will have the full history, including the latest versions. See more at mssqltips.com/sqlservertip/2085/… – Rob Farley Mar 15 '15 at 12:34
  • ObOtherWayToDoIT. I've been using Event Notifications for the past couple years with great success. (So great I now present on it at SQLSatrudays). Here's my current presentation, slide deck, demos, code repository and all. sqlsaturday.com/viewsession.aspx?sat=362&sessionid=25219 – mbourgon Mar 15 '15 at 19:04
  • Yup, Event Notifications do the trick too. :) – Rob Farley Mar 15 '15 at 19:08
0

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.

  • 1
    1) The OLE automation procs (i.e. sp_OA*) have been deprecated since SQL Server 2005. Even if they do technically still work, and even if someone does happen to be on a "sufficiently old" version of SQL Server, it is generally a bad idea to create new functionality for anything other than a one-time need via this method as it is very time-limited. 2) SMO very much can't be called via SQLCLR. You did not read the comments below that answer that you linked to (i.e. the accepted answer is specifically wrong). I have just added an answer to that S.O. question clarifying the issue. – Solomon Rutzky Mar 14 '15 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.