Invoking SMO via T-SQL?

I'd like to use SMO to do several things that aren't easily done in T-SQL. However, all the examples I've found are for C# or Powershell. Is there an easy way to invoke SMO via T-SQL, as you could do with DMO and sp_OACreate? One way around that would be to invoke the SMO via PowerShell via xp_cmdshell - but that's going to be ugly. 开发者_高级运维Suggestions?

The obvious example would be "script out a table using SMO", invoked via SQL.

Thanks.

Unfortunately no, SMO is not directly invoke-able via SQLCLR. Not only is it -- Microsoft.SqlServer.Smo.dll -- not in the Supported .NET Framework Libraries list, but it is specifically disallowed.

If you try to manually import that DLL via CREATE ASSEMBLY, you get the following error:

CREATE ASSEMBLY [SMO]
AUTHORIZATION [dbo]
FROM
N'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
WITH PERMISSION_SET = UNSAFE;

Returns:

Msg 6596, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because assembly 'Microsoft.SqlServer.Smo' is a system assembly. Consider creating a user assembly to wrap desired functionality.

Ok, so try "creating a user assembly to wrap desired functionality".

If your Assembly is marked with PERMISSION_SET = SAFE you get:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "SmoTest":
System.Security.SecurityException: That assembly does not allow partially trusted callers.

If your Assembly is marked with PERMISSION_SET = UNSAFE you get:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "SmoTest":
System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.
System.Exception:
at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
at Microsoft.SqlServer.Management.Smo.Server..ctor()

UPDATE:

And, I just found this related thread from January 2007 on the MSDN forums:

'microsoft.sqlserver.batchparser' is malformed or not a pure .NET assembly.