Выборка из однотипных тaблиц (разные схемы)

by Alexey Knyazev 15. марта 2010 12:34

Начиная с 2005ой версии в SQL Server появилось такое понятие как схема. Схема формально определяется как набор объектов в базе данных, объединенных общим пространством имен. Проще всего представить себе схему как некий логический контейнер в базе данных, которому могут принадлежать таблицы, представления, хранимые процедуры, пользовательские функции, ограничения целостности, пользовательские типы данных и другие объекты базы данных. Этот контейнер удобно использовать как для именования объектов и их логической группировки, так и для предоставления разрешений. Например, если в базе данных есть набор таблиц с финансовой информацией, удобно поместить их в одну схему и предоставлять пользователям разрешения на эту схему (т. е. на этот набор таблиц).

Но возможно вам потребуедся сделать выборку из однотипных таблиц с одинаковым именем, но расположенных в разных схемах. Стандартного средства в T-SQL нет, поэтому написал небольшой динамический скрипт, который выводит результат из нескольких таблиц в виде одного рекордсета.

Для демонстрации создадим новую базу с 10ю разными схемами. Расположим в этих схемах несколько однотипных (имена так же совпадают) таблиц

--Контекст БД мастер
USE master
GO
--Проверяем наличие БД "TestDB" и если она существует-удаляем
IF (SELECT DB_ID('TestDB')) IS NOT NULL
DROP DATABASE TestDB
GO
--Создаём новую БД "TestDB"
CREATE DATABASE TestDB
GO
--Переходим в неё
USE TestDB
GO
--Создаём 10 схем
CREATE SCHEMA Shema1
GO
CREATE SCHEMA Shema2
GO
CREATE SCHEMA Shema3
GO
CREATE SCHEMA Shema4
GO
CREATE SCHEMA Shema5
GO
CREATE SCHEMA Shema6
GO
CREATE SCHEMA Shema7
GO
CREATE SCHEMA Shema8
GO
CREATE SCHEMA Shema9
GO
CREATE SCHEMA Shema10
GO

--В этих схемах создаём однотипные таблицы,
--для наглядности данные содержат имя схемы, таблицы и значене Value"N"
CREATE TABLE Shema1.Table1 (val varchar(255))
INSERT INTO Shema1.Table1
SELECT 'Shema1.Table1_Value1'
UNION ALL
SELECT 'Shema1.Table1_Value2'
UNION ALL
SELECT 'Shema1.Table1_Value3'

CREATE TABLE Shema3.Table1 (val varchar(255))
INSERT INTO Shema3.Table1
SELECT 'Shema3.Table1_Value1'
UNION ALL
SELECT 'Shema3.Table1_Value2'

CREATE TABLE Shema10.Table1 (val varchar(255))
INSERT INTO Shema10.Table1
SELECT 'Shema10.Table1_Value1'
UNION ALL
SELECT 'Shema10.Table1_Value2'
UNION ALL
SELECT 'Shema10.Table1_Value3'
UNION ALL
SELECT 'Shema10.Table1_Value4'

CREATE TABLE Shema2.Table2 (val varchar(255))
INSERT INTO Shema2.Table2
SELECT 'Shema2.Table2_Value1'
UNION ALL
SELECT 'Shema2.Table2_Value2'
UNION ALL
SELECT 'Shema2.Table2_Value3'

CREATE TABLE Shema4.Table2 (val varchar(255))
INSERT INTO Shema4.Table2
SELECT 'Shema4.Table2_Value1'

CREATE TABLE Shema6.Table3 (val varchar(255))
INSERT INTO Shema6.Table3
SELECT 'Shema6.Table3_Value1'

CREATE TABLE Shema5.Table4 (val varchar(255))
INSERT INTO Shema5.Table4
SELECT 'Shema5.Table4_Value1'

CREATE TABLE Shema7.Table4 (val varchar(255))
INSERT INTO Shema7.Table4
SELECT 'Shema7.Table4_Value1'

CREATE TABLE Shema8.Table4 (val varchar(255))
INSERT INTO Shema8.Table4
SELECT 'Shema8.Table4_Value1'

CREATE TABLE Shema9.Table4 (val varchar(255))
INSERT INTO Shema9.Table4
SELECT 'Shema9.Table4_Value1'
UNION ALL
SELECT 'Shema9.Table4_Value2'

А теперь, указав на входе имя таблицы, выберим данные из таблиц с этим именем, но расположенных в разных схемах:

DECLARE @str NVARCHAR(MAX), @Table NVARCHAR(255)
SELECT @Table='Table1'

DECLARE Tables_cur CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME=@Table

OPEN Tables_cur 

FETCH NEXT FROM Tables_cur INTO @Table

	WHILE (@@FETCH_STATUS!=-1) BEGIN
			IF (@@FETCH_STATUS!=-2)BEGIN
				SELECT @str=CASE WHEN @str IS NULL THEN 'SELECT * FROM '+@Table
				ELSE @str+ ' UNION ALL SELECT * FROM '+@Table END
			END
		FETCH NEXT FROM Tables_cur INTO @Table
	END

CLOSE Tables_cur
DEALLOCATE Tables_cur

EXECUTE (@str)

Результат этого запроса:

  1. Shema1.Table1_Value1
  2. Shema1.Table1_Value2
  3. Shema1.Table1_Value3
  4. Shema3.Table1_Value1
  5. Shema3.Table1_Value2
  6. Shema10.Table1_Value1
  7. Shema10.Table1_Value2
  8. Shema10.Table1_Value3
  9. Shema10.Table1_Value4

Запрос объеденил результат из 3х схем (Shema1, Shema3, Shema10), где таблица = 'Table1'.

Tags: ,

SQL Server

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading