Post reply

Make selection in the table in various schemas.

  • December 4, 2011 at 3:12 pm

    #247203

    Hello,

    The structure of my database has a common schema and several schemas with the same table that stores the activities of each company.

    So, I have the schema of the "company x" that has the table activities and thus to other companies, as follows:

    schema "common"

    - Users table

    - Companies table

    schema "companyX"

    - Activities table

    schema "companyY"

    - Activities table

    schema "companyZ"

    - Activities table

    What I need is to select in database, perhaps through a view or stored procedure stored in the common schema objects that returns the data from the activities of all database schemas. Anyone know how can I accomplish this? How to select this?

    Thank you for all the help!

    Cristiano Testai

    Brazi.

  • December 4, 2011 at 5:42 pm

    #1416453

    With a bit of dynamic SQL to interogate the schemas, you could create a view that looks like the following...

    SELECT columnlist

    FROM companyx.Activities

    UNION ALL

    SELECT columnlist

    FROM companyy.Activities

    UNION ALL

    SELECT columnlist

    FROM companyz.Activities

    If each of those tables had a thoughtful constrain column in it to uniquely identify the company, it would also be a very effective "partitioned view" with many advantages similar to a partitioned table. Of course, that's the problem with like-named tables in different schemas... few people include such a column because the schema provides the separation.

    --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)

  • December 5, 2011 at 7:08 am

    #1416668

    Hello,

    I am beginner in the SqlServer and do not know how to create this dynamic sql for my needs.

    You could point the way or assist in an example as I need?

    Thanks.

  • December 5, 2011 at 4:49 pm

    #1417074

    Thanks..

    I did what I need.

  • December 5, 2011 at 7:44 pm

    #1417090

    cristiano.testai (12/5/2011)

    Thanks..

    I did what I need.

    That's cool. Would you mind sharing what you did? Two way street here... 😉

    --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)

  • December 7, 2011 at 6:41 am

    #1418042

    Hi,

    Of course friend! DBA Gustavo Maia help me in the source code:

    -- Create the schemas

    CREATE SCHEMA Emp1

    GO

    CREATE SCHEMA Emp2

    GO

    CREATE SCHEMA Emp3

    GO

    -- Create Tables

    CREATE TABLE Emp1.Atividade (IDUsuario INT, QualquerCoisa VARCHAR(100))

    CREATE TABLE Emp2.Atividade (IDUsuario INT, QualquerCoisa VARCHAR(100))

    CREATE TABLE Emp3.Atividade (IDUsuario INT, QualquerCoisa VARCHAR(100))

    CREATE TABLE dbo.Usuario (IDUsuario INT, NomeUsuario VARCHAR(20))

    -- Insert

    INSERT INTO dbo.Usuario VALUES (1,'Usuario 1')

    INSERT INTO dbo.Usuario VALUES (2,'Usuario 2')

    INSERT INTO dbo.Usuario VALUES (4,'Usuario 4')

    INSERT INTO Emp1.Atividade VALUES (1,'Atividades do usuário da Empresa 1')

    INSERT INTO Emp2.Atividade VALUES (2,'Atividades do usuário da Empresa 2')

    INSERT INTO Emp3.Atividade VALUES (4,'Atividades do usuário da Empresa 3')

    -- The Example Code (Dynamic SQL)

    DECLARE @Tabelas VARCHAR(MAX)

    SET @Tabelas = (

    SELECT 'SELECT * FROM ' + SCHEMA_NAME(SCHEMA_ID) +

    '.Atividade UNION ALL ' As Nome FROM sys.tables

    WHERE name = 'Atividade'

    FOR XML RAW('Tabela'),ROOT('Tabelas'))

    SET @Tabelas =

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(@Tabelas,'<Tabela Nome="',CHAR(10))

    ,'"/>','')

    ,' UNION ALL </Tabelas>','')

    ,'<Tabelas>','')

    DECLARE @cmd VARCHAR(MAX)

    SET @cmd = ';WITH Atividades As (' + @Tabelas + ')' + CHAR(10) + CHAR(10)

    SET @cmd = @cmd + 'SELECT * FROM Usuario As S INNER JOIN Atividades As A ON S.IDUsuario = A.IDUsuario'

    PRINT @cmd

    EXEC (@cmd)

    -- Drop Objects

    DROP TABLE Emp1.Atividade

    DROP TABLE Emp2.Atividade

    DROP TABLE Emp3.Atividade

    DROP TABLE dbo.Usuario

    GO

    DROP SCHEMA Emp1

    DROP SCHEMA Emp2

    DROP SCHEMA Emp3

    Regards,

    Cristiano Testai

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

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