Make selection in the table in various schemas.

Question

Post reply

Make selection in the table in various schemas.

cristiano.testai

Valued Member

Points: 65
More actions
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.

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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)
Intro to Tally Tables and Functions

cristiano.testai Valued Member Points: 65 More actions · Answer 2

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.

cristiano.testai Valued Member Points: 65 More actions · Answer 3

cristiano.testai

Valued Member

Points: 65

December 5, 2011 at 4:49 pm

#1417074

Thanks..

I did what I need.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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)
Intro to Tally Tables and Functions

cristiano.testai Valued Member Points: 65 More actions · Answer 5

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(@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