SQL рецепты

Окт 1

SQL рецепты: поиск по всей базе

Комментариев нет

В дополнение к поиску по триггерам и процедурам публикую скрипт поиска по всем текстовым полям. Ранее это находилось по адресу .


set concat_null_yields_null on
declare
@collist varchar(max),
@srch_sql varchar(max),
@schemaName varchar(128),
@tableName varchar(128)
declare curs cursor local static forward_only for
select distinct c.TABLE_SCHEMA, c.TABLE_NAME
from INFORMATION_SCHEMA.[COLUMNS] c
where c.DATA_TYPE in('char', 'varchar', 'nvarchar', 'text')
and c.CHARACTER_MAXIMUM_LENGTH >=4
and objectproperty(object_id(c.TABLE_SCHEMA + '.'+ c.TABLE_NAME), 'IsUserTable ') = 1
order by 1, 2
open curs
while 1=1
begin
fetch next from curs into @schemaName, @tableName
if @@FETCH_STATUS <> 0 break
-- Данную строку можно раскомментарить, если хочется видеть, в какой таблице идет поиск в данный момент
-- raiserror(';%s.%s', 10, 1, @schemaName, @tableName) with nowait
select
@collist = null
select
@collist = isnull(@collist + '
or ', '') +'upper(convert(varchar(8000), ' + c.COLUMN_NAME + ')) like ''%ИВАН%''' -- Тут указываем, что и как ищем
from INFORMATION_SCHEMA.[COLUMNS] c
where c.TABLE_SCHEMA = @schemaName
and c.TABLE_NAME = @tableName
and c.DATA_TYPE in('char', 'varchar', 'nvarchar', 'text')
and c.CHARACTER_MAXIMUM_LENGTH >=6
set @srch_sql = 'if exists(select * from '+@schemaName+'.'+@tableName+' with(nolock) where '+@collist+')
raiserror('''+@schemaName+'.'+@tableName+' - found!'', 10, 1) with nowait'
exec(@srch_sql)
end

Подробнее

Сен 29

SQL рецепты: курс валют ЦБ РФ

Комментариев нет

Была однажды необходимость загружать курс валют прямо в базу данных. Нужен был самый простой вариант. Вот мы его и написали.


DECLARE @hDoc INT
declare @xml xml
Declare @Object as Int;
Declare @ResponseText as Varbinary(8000);
Declare @Url as Varchar(MAX);
declare @usd float;
declare @today datetime;
select @Url = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req='+convert(varchar(100), getdate(),103)
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responsebody', @ResponseText OUTPUT
Exec sp_OADestroy @Object
select @xml = cast (@ResponseText as xml)
EXEC sp_xml_preparedocument @hDoc OUTPUT,@xml
SELECT
@usd = cast(replace(value, ',','.') as float)
FROM
OPENXML(@hDoc, '//Value')
WITH
(
id nvarchar(100) '../@ID',
name nvarchar(100) '../Name',
value nvarchar(100) '../Value'
)
where id = 'R01235'
EXEC sp_xml_removedocument @hDoc

Таким образом в переменной @usd мы получаем сегодняшний курс доллара. R01235 — идентификатор USD. Документация API

Подробнее

Сен 29

SQL рецепты: скачивание файла и загрузка в базу

Комментариев нет

Сегодня я расскажу, как скачать файл из сети и загрузить его в базу данных.
В последних версиях Windows по умолчанию есть утилита Power Shell, этим мы и воспользуемся.

Напишем скрипт, которому в качестве аргументов передадим URL и Путь.


$webclient = New-Object System.Net.WebClient
$url = $args[0]
$file = $args[1]
$webclient.DownloadFile($url,$file)

Назовем файл download.ps1.

Затем нам всего лишь остается написать немного кода на t-sql


set @cmd = 'powershell.exe -file "C:\alpha\download.ps1" "'+@fullurl+'" "'+@filepath+'" '
EXEC xp_cmdshell @cmd
if object_id('tempdb..#Filedata') is not null
drop table #Filedata
create table #filedata(number int, filedata varbinary(max))
set @sql = 'insert into #filedata(number, filedata)
SELECT '+CAST(@IncidentNumber as varchar(max))+', CAST(bulkcolumn AS varbinary(max))
FROM OPENROWSET(BULK '''+@filepath+''', SINGLE_BLOB) as y'
exec (@sql)
select @datafile=filedata from #filedata

Таким несложным способом из комбинации Power Shell, Openrowset и динамического SQL мы получили во временной таблице наш файл в бинарном виде. Применяйте везде, где нужно.

Подробнее

Сен 28

SQL рецепты: удаление всех записей из таблиц

Комментариев нет

Еще один незаменимый рецепт, который просто спас однажды меня. Во-первых, это процедура, которая умеет делать все что хочешь с каждой таблицей в базе данных sp_msforeachtable, во-вторых, конечно же ее применение в реальной жизни.


exec sp_msforeachtable N'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_msforeachtable N'ALTER TABLE ? DISABLE TRIGGER ALL'
exec sp_msforeachtable N'DELETE FROM ?'
exec sp_msforeachtable N'ALTER TABLE ? ENABLE TRIGGER ALL'
exec sp_msforeachtable N'ALTER TABLE ? CHECK CONSTRAINT ALL'

Этот код последовательно отключает проверку внешних ключей, все триггеры, затем удаляет все записи из таблиц, затем включает триггеры и проверку ключей обратно.
Обращаю внимание, что delete работает очень медленно и пишет в логи, гораздо быстрее работает truncate. Но truncate будет работать только если удалить все ключи (и те, которые ссылаются, и те, на которые ссылается таблица). Это легко делается через интерфейс SQL Management Studio, но восстанавливается, увы, не так быстро и только руками. В общем, выбирайте сами.

Подробнее

Сен 28

SQL рецепты: поиск по тексту процедур и триггеров

Один комментарий

Сим постом начинаю новую рубрику «SQL рецепты», ибо в работе часто приходится искать что-то, разбросанное по сети. Все в этой рубрике будет касаться MS SQL.

Дебютирует в этой рубрике самый популярный рецепт. Поиск по тексту триггеров и процедур:

SELECT o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id = o.object_id
WHERE m.definition Like '%search_string%'

Вместо search_string следует вставить свою строку.

Подробнее