SQL Server OpenRowSet on Unknown Table

In summary, the conversation is about a SQL query that retrieves information from the sys.computed_columns table and uses the OPENROWSET function to retrieve data from the SQLSRC table. The purpose of the SQLSRC table is to store the source code for stored procedures in MS SQL Server.
  • #1
Pepper Mint
91
139
SQL:
SELECT c.object_id AS id,
        convert(smallint, c.column_id) AS number,
       s.colid, s.status,
        convert(varbinary(8000), s.text) AS ctext,
        convert(smallint, 2 + 4 * (s.status & 1)) AS texttype,
        convert(smallint, 0) AS language,     
       s.text
    FROM sys.computed_columns c CROSS APPLY OPENROWSET(TABLE SQLSRC, c.object_id, c.column_id) s

Would someone please help me explain what TABLE SQLSRC in the code means ? I am splitting hairs to just get a glimpse of it but still unable to figure it out.
 
Last edited by a moderator:
Technology news on Phys.org

1. What is SQL Server OpenRowSet?

SQL Server OpenRowSet is a Transact-SQL function that allows you to access data from an external data source, such as another SQL Server instance or a different database management system, and use it in your local SQL Server database.

2. How do I use OpenRowSet on an unknown table?

You can use the OpenRowSet function to specify the name of the external data source and the query you want to run on it. If the table name is unknown, you can use a wildcard character to retrieve all tables from the external data source.

3. What are the benefits of using OpenRowSet on unknown tables?

Using OpenRowSet on unknown tables allows you to access and manipulate data from external sources without having to know the exact structure or name of the tables in advance. This can be useful when working with data from multiple sources or when the external data source is frequently changing.

4. Are there any limitations to using OpenRowSet on unknown tables?

Yes, there are some limitations to using OpenRowSet on unknown tables. For example, you cannot use the asterisk (*) wildcard character in the select list when querying an external data source. Additionally, certain data types may not be supported by OpenRowSet, so it is important to check the documentation for a list of supported data types.

5. Can I update or delete data from an unknown table using OpenRowSet?

Yes, it is possible to update or delete data from an unknown table using OpenRowSet. However, you must be careful when doing so as it may affect data in the external data source. It is recommended to use a WHERE clause to specify the records to be updated or deleted, and to always test the query before running it on a production environment.

Similar threads

  • Programming and Computer Science
Replies
3
Views
1K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
3
Views
279
Replies
16
Views
2K
  • Advanced Physics Homework Help
Replies
4
Views
5K
  • Engineering and Comp Sci Homework Help
Replies
0
Views
1K
  • Special and General Relativity
Replies
13
Views
2K
Replies
128
Views
31K
  • General Discussion
Replies
1
Views
8K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
1
Views
2K
  • Sticky
  • Feedback and Announcements
Replies
2
Views
495K
Back
Top