SMO scripting objects and security

Question

I have a customer that has a SQL database on a hosted server; call the db "myDatabase". The hosting co. has locked down object explorer - I can't myDatabase thed database listed (I see tempdb and master). However, if I "use myDatabase" and then "select * from myTable", all works fine.

Since we have no access to object explorer, I can't right click and generate scripts. I thought that I might be able to use SMO to accomplish what I want, but when I attempt something similar to this:

Server myServer = new Server(conn);
Database myDB = server.Databases["myDatabase"];
Table myTbl = myDB.Tables["myTable"];

It fails - myDB is null (when I iterate through the databases collection, as expected, I only see master and tempdb - the db's I can see in object explorer). It obviously has to do with security - if I can't see the table in object explorer, it won't let me access it through SMO. Anyone have any ideas of a workaround or alternate method to allow me to generate a script?

Thx!

Answer 1

I haven't looked at the SMO code, but have you tried using the constructor on the database object? Maybe you can access it directly.

Database myDB = new Database(myServer, "myDatabase");

Answer 2

Is the myDb.Tables collection empty? Could it be that you are referencing it using the wrong name?

One option you could try is to use Linq2Sql to generate a model of the database. You can then use the model to create a new database that should be more or less identical to the original. Look up the DataContext.CreateDatabase method for more info.

Another option would be to list all tables using the following query:

select * from sys.tables

And then listing all columns in the tables using the following:

select * from sys.columns where object_id = (object id from the previous query)

This will give you all tables and columns defined in your database and should be enough to create the database structure. In addition you have system views for other objects defined as well.