Getting Linked Server Database Tables and Views via SMO

Question

How do I get SQL Server databases that are on a Linked Server via SMO?

Server server = GetServer("server");
Database db = server.Databases["db"];
LinkedServer ls = server.LinkedServers["ls"];

The second line above returns a regular database. The third line returns a particular linked server, which provides access to the linked server connection, but not to its data. How can I get something like:

Database db1 = server.LinkedServers["ls"].Databases["db"];

? The reason I need this is that I will be looping through different objects within the linked database, such as tables or views.

UPDATE

For further clarification, I currently have this code:

public void GenerateViews(string objectName = null)
{
    Server server = new Server("server");
    //Database a = server.Databases["a"];
    Database b = server.Databases["b"];

    b.Tables.OfType<Table>().ToList().ForEach(o => ProcessSqlObject(o));
    b.Views.OfType<View>().ToList().ForEach(o => ProcessSqlObject(o));
}

//takes all tables and views in database b that have a custom extended property "CreateView", and create a view for it in database a
private void ProcessSqlObject(dynamic o)    //o MUST be an SMO table or view (since they don't implement a common interface, I'm using a dynamic)
{
    Database ct = (Database)o.Parent;
    Database a = ct.Parent.Databases["a"];

    const string viewPrefix = "V_CTC_";   
    const string SourceIDColumnName = "SourceID";    
    string objectName = (string)o.Name;   //name of table or view
    objectName = objectName.StartsWith("V_", StringComparison.InvariantCultureIgnoreCase) ? objectName.Substring(2) : objectName;
    string viewName = viewPrefix + objectName;  //remove V_ from view, so that we don't have "V_V_".

    ExtendedProperty ep = (ExtendedProperty)o.ExtendedProperties["CreateView"];
    bool AlreadyExists = a.Views.OfType<View>().Any(v => v.Name == viewName);

    if (ep != null && ep.Value.ToString() == "1")  //there IS an extended property, and its value is 1, meaning, we want a view
    {
        if (!AlreadyExists) //we don't already have the view    
        {

            //ProcessSqlObject(t, viewName, SourceIDColumnName, ct, a);

            StringBuilder ws = new StringBuilder();
            ws.AppendLine("SELECT");
            ws.AppendLine("\t2 [" + SourceIDColumnName + "]");

            ((ColumnCollection)o.Columns).OfType<Column>().ToList().ForEach(c =>
            {
                ws.AppendLine("\t, [" + c.Name + "]");
            });

            string linkedServer = "[ls].";
            ws.AppendLine("FROM " + linkedServer + "[" + ct.Name + "].[dbo].[" + o.Name + "] WITH(NOLOCK)");

            string rt = ws.ToString();
            rt = rt.Replace("wholesale", "retail");
            rt = rt.Replace("2 [" + SourceIDColumnName + "]", "3 [" + SourceIDColumnName + "]");
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("CREATE VIEW " + viewName + " AS");
            sql.AppendLine();
            sql.AppendLine(ws.ToString());
            //sql.AppendLine();
            sql.AppendLine("UNION ALL");
            sql.AppendLine();
            sql.AppendLine(rt);

            Console.WriteLine(sql);

            a.ExecuteNonQuery(sql.ToString());
        }

    }
    else    //we DON't want the view
    {

        a.Views.OfType<View>().Single(v => v.Name == viewName).Drop();
        a.Refresh();

        }
    }
}

I am currently passing to the second function all tables and views in a given database. This is withOUT using a linked server. I want the ability to do the same thing but for a linked server, without having to rewrite the code.

Thanks.

score 1 · Answer 1 · answered Mar 09 '15 at 16:17

1

You don't need to connect to the server to get the tables and views (if you just need their names). The LinkedServer class provide the EnumTables method for that.

answered Mar 09 '15 at 16:17

Juan

3,675
20
34

I already have a method that loops through tables for non-linked-server db's, and I want to pass this to that method, without having to rewrite everything. Is there any other way? – as9876 Mar 09 '15 at 16:41
Can you update the question with your method signature? And what it does with the tables/views? – Juan Mar 09 '15 at 16:43
Right, so you are scripting out the database... lets se if we find the way getting them in the same objects you expect... – Juan Mar 09 '15 at 17:24

score 1 · Answer 2 · answered Mar 09 '15 at 18:59

1

The LinkedServer class has a DataSource property that you should be able to use as the name of the remote server. If you pass this to your GetServer() function, you should get back an SMO Server object.

answered Mar 09 '15 at 18:59

Ben Thul

31,080
4
45
68

Nice workaround! However, suppose the IP or port of the linked server was down, so it couldn't be accessed directly, but it could be accessed via the main server (suppose they were both on the same physical machine), in that scenario, this wouldn't work. – as9876 Mar 09 '15 at 23:16
Similarly, in my case, the linked server is on a different LAN, and its public name is different than its internal one :( – as9876 Mar 09 '15 at 23:21
It should return whatever name you entered when you created the linked server. There's no magic here. If you're accessing the server that the linked server points to from a different network, that's another problem but this solution solves the question you posed. – Ben Thul Mar 09 '15 at 23:36

Getting Linked Server Database Tables and Views via SMO

2 Answers2