Alternatives to SMO, i.e. DML APIs for SQL Server?

Question

I just learned I can't use SMO in ASP.NET without either SQL or a full SMO install on the application server. I'm not allowed to do that, so I can't use SMO.

What other libraries do the same thing as SMO but don't require an MSI installer or COM registrations?

I know I could send DDL to the server inside ADO.NET commands, but that is exactly what I was trying to avoid by using SMO.

What was nice about SMO:

Object oriented API for querying meta-data (columns, data types) that didn't rely on inconsistent COBOL-like DDL.

Didn't require querying undocumented stored procedures, system stored procedures or tables which break every few versions.

Off the top of my head I can think of ADOX and DMO, but both were COM based APIs.

Answer 1

SMO is running T-SQL under the covers. You could prototype in SMO and then watch in profiler to get the T-SQL.

It is probably an EULA violation, but you could redistrib the SMO assemblies side-by-side with your app, nothing to install in that case. I don't think their installer hits the registry. Pretty easy to bust open the SQLServerManagementObjects.msi and find out.