Querying sys.objects

Posted on 2009-04-20
Last Modified: 2013-12-07
Is there a way to query another databases sys.objects collection without creating a linked server? For example, I need to check whether a table exists in another database before doing something. Normally I would query sys.objects to get this but I don't want to create a linked server for this case to get it. Any ideas?
Question by:brianwells05
    LVL 9

    Accepted Solution

    If its on a different server you can try using the OPENDATASOURCE command.  See Books Online for syntax.
    LVL 4

    Assisted Solution

    I have not used this but Dan is correct.  I took this example from books online and modified it to hit sys.objects.  You just need to put in your remote server name

    SELECT *
        'Data Source=RemoteServerName;Integrated Security=SSPI')

    Open in new window


    Author Comment

    I was looking for something simple like this: "select * from [Database].sys.tables"
    I should have mentioned that I was looking on the same server...but that's a very useful technique for remote servers....thanks for the input.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now