#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
В нашей базе данных у нас есть 135 таблиц, в которых есть столбец с именем EquipmentId. Мне нужно запросить каждую из этих таблиц, чтобы определить, имеет ли какая-либо из них EquipmentId, равный определенному значению. Есть ли способ сделать это в одном запросе вместо 135 отдельных запросов?
Большое спасибо.
Комментарии:
1. Если есть способ сделать это в запросе signle, я хотел бы его увидеть. Я думаю, что это очень хороший вопрос. Мне пришлось сделать это однажды, и я решил проблему, написав приложение для ее решения. Я полагаю, что приложение запросило SysObjects, чтобы получить имена таблиц с этим именем столбца, а затем выполнило цикл по этим таблицам для отдельных запросов.
2. Мне кажется, что ваша схема базы данных должна отличаться для ваших конкретных требований.
3. Может быть, он, как и я, запрашивал базу данных у приложения, написанного третьей стороной. Я начал как разработчик с получения данных из баз данных других поставщиков, и мне все еще приходится делать это время от времени.
4. У вас есть 135 таблиц, в каждой из которых хранятся объекты оборудования? Или это столбцы FK, и вы хотите проверить, существует ли, скажем, перед УДАЛЕНИЕМ? Не хочу здесь делать никаких предположений 🙂
5. 1 @gbn: Именно то, о чем я думал. На первый взгляд нет ничего плохого в дизайне, содержащем 135 таблиц с FK для таблицы оборудования.
Ответ №1:
Вы используете либо динамический SQL для генерации запросов ко всем таблицам и, возможно, объединения результатов, либо что-то вроде недокументированной хранимой процедуры sp_MSforeachtable.
sp_msforeachtable 'select * from ? where equipmentid = 5'
Комментарии:
1. Это завершится ошибкой, если хотя бы в одной таблице в базе данных нет столбца equipmentid.
Ответ №2:
Вы могли бы использовать запрос для построения запроса:
select 'union all select * from ' name
' where EquipmentId = 42' char(13) char(10)
from sys.tables
Скопируйте результат, удалите первую union all и запустите запрос 🙂
Комментарии:
1. Вы набрали это быстрее меня! Собирался предложить то же самое 🙂
2. Этот запрос завершится ошибкой, если хотя бы в одной таблице базы данных нет столбца equipmentid.
Ответ №3:
Я бы сбросил их во временную таблицу или что-то еще подобное:
CREATE TABLE #TempTable (Equip NVARCHAR(50))
sp_msforeachtable 'INSERT INTO #TempTable (Equip) SELECT Equip FROM ?'
SELECT * FROM #TempTable
DROP TABLE #TempTable
Ответ №4:
Я предполагаю, что не все таблицы в базе данных имеют столбец EquipmentId. Если это допустимое предположение, то параметр @whereand в sp_msforeachtable поможет отфильтровать таблицы. Приведенный ниже запрос покажет имена всех таблиц, в которых указан EquipmentId. Имя таблицы будет показано столько раз, сколько строк из этой таблицы имеют указанный EquipmentId.
declare @EquipmentId int = 666
create table #Result (TableName sysname)
declare @command nvarchar(4000) =
'insert into #Result select ''?'' from ? where EquipmentId = ' cast(@EquipmentId as varchar)
execute sp_msforeachtable
@command1 = @command,
@whereand = 'and o.id in (select object_id from sys.columns where name = ''EquipmentId'')'
select *
from #Result
drop table #Result
Комментарии:
1. 1 Этот подход работает лучше, чем мой. Не представлял, что вы можете разделить where таким образом. Спасибо!
Ответ №5:
Вероятно, вам придется использовать динамический SQL для этого — запросить системные таблицы для всех таблиц, в которых есть столбцы с именем EquipmentId , и создать динамическую инструкцию SQL, запрашивающую каждую таблицу на наличие того конкретного EquipmentId, который вам нужен.
РЕДАКТИРОВАТЬ: @mellamokb кажется намного проще — попробуйте это.
Ответ №6:
Это может быть реализовано с помощью LEFT JOIN’s. Сначала нам понадобится базовая таблица для хранения определенных значений идентификаторов оборудования, которые мы ищем:
CREATE TABLE #CertainValues
(
EquipmentID int
)
INSERT INTO #CertainValues(EquipmentID) VALUES (1)
INSERT INTO #CertainValues(EquipmentID) VALUES (2)
INSERT INTO #CertainValues(EquipmentID) VALUES (3)
Затем мы можем объединить 135 известных таблиц с этой базовой таблицей, используя их соответствующие поля [EquipmentID]. Чтобы избежать проблем с количеством элементов (дублирование) из-за [EquipmentID], появляющегося в нескольких строках одной таблицы, лучше всего использовать подзапрос для получения количества элементов для [EquipmentID] в каждой из 135 таблиц.
SELECT
CV.EquipmentID,
ISNULL(T001.CNT, 0) AS T001,
ISNULL(T002.CNT, 0) AS T002,
...
ISNULL(T134.CNT, 0) AS T134,
ISNULL(T135.CNT, 0) AS T135
FROM
#CertainValues AS CV
LEFT OUTER JOIN (SELECT EquipmentID, SUM(1) AS CNT FROM Table001 GROUP BY EquipmentID) AS T001 ON CV.EquipmentID = T001.EquipmentID
LEFT OUTER JOIN (SELECT EquipmentID, SUM(1) AS CNT FROM Table002 GROUP BY EquipmentID) AS T002 ON CV.EquipmentID = T002.EquipmentID
...
LEFT OUTER JOIN (SELECT EquipmentID, SUM(1) AS CNT FROM Table134 GROUP BY EquipmentID) AS T134 ON CV.EquipmentID = T134.EquipmentID
LEFT OUTER JOIN (SELECT EquipmentID, SUM(1) AS CNT FROM Table135 GROUP BY EquipmentID) AS T135 ON CV.EquipmentID = T135.EquipmentID
Это также дает нам более значимый результирующий набор, который показывает количество строк в таблице для каждого из определенных значений, которые мы ищем. Ниже приведен пример результирующего набора:
EquipmentID T001 T002 ... T134 T135
----------- ---- ---- ... ---- ----
1 0 1 ... 2 3
2 3 2 ... 1 0
3 0 0 ... 0 0