run query on managed tables

Shambhu Rai 1,401 Reputation points
2023-07-31T16:45:50.1033333+00:00

Hi Expert,

unable to run the query select * from table_name in sql query editor in azure portal but when i execute select * from dbo.tbls command it is visible ...how can i query the table name

User's image

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Vahid Ghafarpour 14,775 Reputation points
    2023-07-31T17:23:45.5833333+00:00

    A different schema may own the table. If the table is in a different schema, you should include the schema name in the query: "select * from schema_name.table_name."

    For example, in your screenshot, you have two records with TBL_NAME: student_external1dd


  2. AnnuKumari-MSFT 29,601 Reputation points Microsoft Employee
    2023-08-01T08:01:07.7266667+00:00

    Hi Shambhu Rai ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As per my understanding, you want to generate a dynamic SQL query that selects all columns from a table specified by its name, you can create procedure in SQL like below which takes the tablename as input and run the select statement for that table:

    CREATE PROCEDURE GetTableData
        @tbl_name NVARCHAR(100)
    AS
    BEGIN
        DECLARE @query NVARCHAR(MAX);
    
        SET @query = N'SELECT * FROM ' + QUOTENAME(@tbl_name);
    
        EXEC sp_executesql @query;
    END;
    

    Run the above stored procedure by using the below command:

    exec GetTableData 'student_external1dd123'
    

    Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou