Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Получить список таблиц по условию  [new]
ИринаВ
Member

Откуда: Москва
Сообщений: 470
Есть база, таблицы могут добавляться/удаляться, под задачи юзера.
Нужно получить список таблиц, содерж. записи, в которых поле (GroupID) удоветворяет условию и вернуть список в прогу на Delphi.
Рою задачу с 2-х сторон: из SQL, и из Delphi. Забацала процедуру, возвращает строку, со списком таблиц.
CREATE PROCEDURE [dbo].[EnumTablesByGroupID] @Cond varchar(MAX)
AS
BEGIN 
  DECLARE tcrs CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT sys.tables.name FROM sys.tables
    WHERE EXISTS(SELECT * FROM sys.columns 
                 WHERE sys.tables.object_id=sys.columns.object_id AND sys.columns.name='GroupID')
    ORDER BY 1

  DECLARE @tablist varchar(MAX), @tabnam varchar(100), @resid int, @sqlt nvarchar(MAX), @parm nvarchar(100)
  SET @tablist=''
  OPEN tcrs
  FETCH NEXT FROM tcrs INTO @tabnam
  WHILE @@fetch_status=0 BEGIN
    SET @sqlt=N'SELECT TOP 1 @RES_ID=ID FROM '+@tabnam+' WHERE '+@cond
    SET @parm=N'@RES_ID int OUTPUT';
    EXECUTE sp_executesql @sqlt,@parm,@RES_ID=@resid OUTPUT
    IF @resid>0 SET @tablist=@tablist+@tabnam+Char(13)+Char(10)
    FETCH NEXT FROM tcrs INTO @tabnam
  END
  CLOSE tcrs
  DEALLOCATE tcrs

  SELECT @tablist
END
Покритикуйте плзз. Хочу добится макс. скорости.
26 окт 11, 16:19    [11504152]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИринаВ,

курсор в топку сразу.
declare @sql nvarchar(max)
set @sql = STUFF((
SELECT N'UNION ALL SELECT * FROM '+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) + NCHAR(13)  FROM sys.columns c join sys.objects t on c.object_id = t.object_id
WHERE c.name='GroupID'
FOR XML PATH(''),type).value('.','nvarchar(max)'), 1, 10,'')

set @sql = N'SELECT * FROM (
' + @sql +')t
WHERE GroupID = ...'

print @sql
26 окт 11, 16:31    [11504285]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
ИринаВ
Member

Откуда: Москва
Сообщений: 470
iljy , спасибо. Но не вьехала про 'FOR XML'. Для чего он здесь?
Сделала как поняла, и действ., скорость больше.

CREATE PROCEDURE [dbo].[EnumTablesByGroupID_1] @Cond varchar(MAX)
AS
BEGIN 
  SET CONCAT_NULL_YIELDS_NULL OFF
  DECLARE @sqlt nvarchar(MAX)

  SELECT @sqlt=@sqlt+N'UNION ALL SELECT '+NCHAR(39)+sys.tables.name+NCHAR(39)+N' AS TabNam, GroupID '+
                                'FROM '+sys.tables.name+NCHAR(13)+NCHAR(10)
  FROM sys.columns INNER JOIN sys.tables ON sys.columns.object_id=sys.tables.object_id
  WHERE sys.columns.name='GroupID'
  ORDER BY sys.tables.name

  SET @sqlt=N'SELECT DISTINCT TabNam FROM ('+STUFF(@sqlt,1,10,'')+') AS TmpTab WHERE '+@cond
  EXECUTE sp_executesql @sqlt
END

Еще раз сэнкс
26 окт 11, 20:42    [11505708]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ИринаВ,

SET CONCAT_NULL_YIELDS_NULL OFF
Очень скоро запретят.
Будет только ON

Про
FOR XML PATH('')
ищите на этом форуме - десятки тем. Всё станет ясно.
26 окт 11, 21:16    [11505807]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИринаВ,

выполнение select @s = @s + ... не докуметировано, соответственно результат может получится весьма неожиданный. С FOR XML все документировано и стандартно, а главное - работает быстрее, на 2008 точно.
26 окт 11, 21:55    [11505918]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
ИринаВ
Member

Откуда: Москва
Сообщений: 470
iljy
выполнение select @s = @s + ... не докуметировано, соответственно результат может получится весьма неожиданный

Сэнкс, но нельзя ли подробне? Часто так делаю, но не было ничего, вроде. Теперь боюсь.

Про "... PATH(''),TYPE).value" нашла кое что, в основном понятно.
26 окт 11, 22:19    [11505976]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
iljy
Member

Откуда:
Сообщений: 8711
ИринаВ,

где-то на technet даже примеры были, счас ссылку лениво искать. Суть в том, что оптимизатор может выбрать только последнюю строку (документировано, что для select @x = ... присвоится последнее выбранное значение, соответственно выборка промежуточных может быть опущена. Для простого присваивания разницы никакой, а для накопительного - сами понимаете). Ну и даже при правильном выполнении разница в производительности заметная, потому как FOR XML - это просто последовательная сборка из потока, а @sql = @sql + ... - это операции над строками, причем с каждым разом все более длинными, а строка в скуле объект динамический и не очень шустрый.

Сравните два скрипта
1.
declare @s nvarchar(max) = ''
declare @d datetime = getdate()

set @s = (select N',' + CAST(N as nvarchar(20)) from Numbers
		  where N between 1 and 20000
		  for xml path(''),type).value('.','nvarchar(max)')

select DATEDIFF(ms,@d, getdate())
2.
declare @s nvarchar(max) = ''
declare @d datetime = getdate()

select @s = @s + N',' + CAST(N as nvarchar(20)) from Numbers
where N between 1 and 20000

select DATEDIFF(ms,@d, getdate())

Numbers - таблица чисел. У меня их там 1млн.
26 окт 11, 23:03    [11506064]     Ответить | Цитировать Сообщить модератору
 Re: Получить список таблиц по условию  [new]
ИринаВ
Member

Откуда: Москва
Сообщений: 470
Спасибо. Завтра протестю.
26 окт 11, 23:38    [11506153]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить