How about generate objects DDL from T-SQL ?

Some time ago, I’ve been trying to figure out how to generate the DDL of objects from the T-SQL command line. Powershell allows us to do that, from the PS command-line, as well as SMO. But SMO is not usable with SQLCLR so far so the list of our options goes thin.

We’d be tempted to play with the evil-feature itself, AKA COM Automation …

Why not ?

If you google around you’ll read many warnings against the usage of COM (or OLE) Automation with SQL Server. And in most cases, they’ll be legitimate, but only because there is an assumption that the external COM library is always loaded in SQL Server address space, and can jeopardize the process and all of its memory structures. Actually there is another way around.

You can use a DLL host process to host that library, and make it available to SQL Server using a COM server and ole automation stored procedures (sp_OA…). This is done using the @context parameter of the sp_OACreate stored procedure. Quoting from MSDN:

1 = In-process (.dll) OLE server only.
4 = Local (.exe) OLE server only.
5 = Both in-process and local OLE server allowed

When you specify a context value of 4, a local OLE server does not have access to any SQL Server resources, and it cannot damage SQL Server memory or resources.

This means when you pass @context=4, the DLL is loaded in a separate dllhost.exe process, and cannot trash any memory structure inside SQL Server address space anymore. Good.

Write and register the COM library:

A sample program (I am by no mean a C# guru here :)) to call the SMO Script() method for reverse engineering:

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace getDDL
{
     public interface IgetDDL          {
         string ddlThis(string _srvname, string _dbname, int _objid);
     }
     public class cgetDDL : IgetDDL           {
         string ddlstr;

         public cgetDDL() { }     
         public string ddlThis(string _srvname, string _dbname, int _objid)             {
             Server Srv = new Server(_srvname);
             Database Db = new Database();
             Db = Srv.Databases[_dbname];
             StringCollection _tablescr = Db.Tables.ItemById(_objid).Script();

             foreach (string _line in _tablescr)
                 ddlstr += _line;
             ddlstr += "GO"+'\n';

             IndexCollection _indexes = _t.Indexes;
             foreach (Index _i in _indexes)
             {
                 StringCollection _iscr = _i.Script();
                 foreach (string _line in _iscr)
                     ddlstr += _line + '\n';
             }
             if (_indexes.Count != 0)
                 ddlstr += "GO" + '\n';

             CheckCollection _checks = _t.Checks;
             foreach (Check _c in _checks)
             {
                 StringCollection _cscr = _c.Script();
                 foreach (string _line in _cscr)
                     ddlstr += _line + '\n';
             }
             if (_checks.Count != 0)
                 ddlstr += "GO" + '\n';

             TriggerCollection _triggers = _t.Triggers;
             foreach (Trigger _trg in _triggers)
             {
                 StringCollection _trgscr = _trg.Script();
                 foreach (string _line in _trgscr)
                     ddlstr += _line + '\n';
             }
             if (_triggers.Count != 0)
                 ddlstr += "GO" + '\n';

             foreach (Column _col in _t.Columns)
             {
                 if (_col.DefaultConstraint != null)
                 {
                     StringCollection _def = _col.DefaultConstraint.Script();
                     foreach (string _line in _def)
                         ddlstr += _line + '\n';
                     ddlstr += "GO" + '\n';
                 }
            }

             return ddlstr;
         }
     }
}

The method ddlThis() will be published by the public interface IgetDDL(). You’ll need to put the ComVisible to true in the assembly.cs file, and sign the assembly:

[assembly: ComVisible(true)]

Comes the COM part

Now we need to register the library and expose the type library file (.tlb) to the COM Server. We’ll use regasm to do that:

Your .Net path here >regasm /codebase getDDL.dll /tlb:getDDL.tlb
Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.3053
Copyright (C) Microsoft Corporation 1998-2004. Tous droits réservés.
Inscription des types réussie
L'assembly a été exporté vers 'C:\DBA\DEV\C#\getDDL\bin\Debug\getDDL.tlb' et la bibliothèque de types a été inscrite avec succès

Once the TLB file has been generated, we can fire the comexp.msc snap-in and create an empty COM+ app called getDDL. I’ll set the same service account for the COM server as the service account for SQL Server, so it has elevated privileges, but you can create a special service account and declare it as sysadmin in SQL Server.

And finally, just drag and drop the TLB file inside the components of your new application and check that the exposed ddlThis() is visible as expected:

The service can be started and the presence of the dllhost.exe checked in task manager. Next step we’ll get back to do some T-SQL.

Last part, the sp_OA procedure:

First thing, we need to authorize SQL Server to run our very dangerous code:

sp_configure 'Ole Automation Procedures',1;
GO
reconfigure;
GO

And create our usp_getDDL encapsulating all the OA calls to the COM server:

create procedure usp_getDDL (@tablename varchar(128))
AS
BEGIN
     DECLARE @hr int, @obj int;
     DECLARE @Source nvarchar(255), @Desc nvarchar(255);
     DECLARE @DDLTEXT nvarchar(4000);
     DECLARE @dbname sysname, @objid int, @srvname sysname;

     select @srvname = @@servername
     select @dbname= db_name()
     select @objid = object_id(@tablename)
     if (@objid is NULL)
     BEGIN
         print 'Table '+@tablename+' non trouvee dans la base courante'
         RETURN
     END

     execute @hr = sp_OACreate 'getDDL.cgetDDL', @obj OUT, 4          --> @context = 4, DLL hosted in a separate process
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OAMethod @obj, 'ddlThis', @DDLTEXT OUT, @srvname, @dbname, @objid
     if (@hr <> 0)
     BEGIN
         EXEC sp_OAGetErrorInfo @obj,
         @Source OUT,
         @Desc OUT;
         SELECT HR = convert(varbinary(4),@hr),
         Source=@Source,
         Description=@Desc;
         RETURN
     END

     execute @hr = sp_OADestroy @obj

     select @DDLTEXT as 'DDL'
END

You’ll notice the sp_OACreate @context=4 which is of extreme importance in our case. At last, we are able to retrieve some object DDL using the stored procedure from the SQLCMD prompt:

DOS>sqlcmd -E
1> use CAPDATA
2> go
Le contexte de la base de données a changé ; il est maintenant 'CAPDATA'.
1> exec usp_getddl 'ANNONCE'
2> go

DDL
------------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ANNONCE](
 [ID_ANNONCE] [int] NOT NULL,
 [ID_JOURNAL] [int] NOT NULL,
 [ID_PROPALOUER] [int] NOT NULL,
 [DATETIME_ANNONCE] [datetime] NOT NULL,
 [PRIX] [numeric](6, 2) NULL,
 [NB_PARUTION] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ANNONCE] ADD  CONSTRAINT [PK_ANNONCE] PRIMARY KEY CLUSTERED
(
 [ID_ANNONCE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Hopefully we have found a modest but safe way to make COM automation and SQL Server work together. BTW, I have found another very interesting initiative on Codeplex using SMO and Service Broker, by a fellow SQL Server MVP Simon Sabin.

Cheers,
~David

Advertisements

About dbaffaleuf

I've been in the IT for a decade administering mission critical RDBMS systems such as Sybase ASE and Microsoft SQL Server, for some of the largests investment banks, asset managers and telecoms in France. I came to the position of principal consultant on SQL Server technologies at CapData Consulting in 2006, where I still manage a team of senior DBAs, do expertise jobs and software support for our remote monitoring solutions. I also have the privilege of sharing my experience by teaching classes on SQL Server topics, and blogging in french at http://blog.capdata.fr and in english at SQL Server Central. (http://www.sqlservercentral.com/Authors/Articles/David__Baffaleuf/405129/).
This entry was posted in .NET, Coding and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s