Post reply

how to do the following operation while the tables under different schema?

  • March 22, 2021 at 9:57 am

    #3860727

    how to do the following operation while the tables under different schema?

    for example, there are two tables with same name but under different schema in the same database,

    one table saletable under schema A and the other table saletable under schema B, how to do this operation ,

    such as sp_helpindex , sp_spaceused and dbcc dbreindex(tablename) for the saletable under schema A and schema B.  thanks!

  • March 22, 2021 at 12:30 pm

    #3860761

    You simply have to supply the schema name when referencing the object:

    myschema.TheTable
    yourschema.TheTable

     

    Instead of just reference the table name alone.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 22, 2021 at 12:56 pm

    #3860790

    Grant Fritchey wrote:

    You simply have to supply the schema name when referencing the object:

    myschema.TheTable
    yourschema.TheTable

    if I use schemaname.tablename,  system wll show "Incorrect syntax near '.'." as the picture

    Instead of just reference the table name alone.

    Attachments:
    You must be logged in to view attached files.

  • March 22, 2021 at 1:18 pm

    #3860793

    sorry , the said picture should be like this one, thanks!

    Attachments:
    You must be logged in to view attached files.

  • March 22, 2021 at 2:07 pm

    #3860824

    thanks !

     

    I know use 'schemaname.tablename' , then it works fine, thanks!

    need to use quotation mark.

  • March 22, 2021 at 2:35 pm

    #3860834

    This is one of those places where the first step should have been an online search for "sp_spaceused" to find the documentation on the subject.  It's pretty explicit about having to use single quotes on the qualified object name and doing such a search can save you a pot wad of time.

    There's also a bit more to sp_spaceused that no one ever "gets" that the documentation will also help with.  Here's the first link that comes up in an online search...

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-ver15

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply