Архив рубрики: T-SQL

xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

xp_cmdshell 'DIR c:\'

Поиск по всей базе

set nocount off
declare @name varchar(128),
        @substr nvarchar(4000),
        @column varchar(128), 
        @schema varchar(100)
        
set @substr = '%что_ищем%' 

create table #rslt (  
  table_name varchar(128),
  field_name varchar(128), 
  value ntext,
  shema_name varchar(128)
)

declare s cursor fast_forward for 
select table_name, TABLE_SCHEMA
from information_schema.tables (nolock)
where (table_type = 'BASE TABLE' OR table_type = 'VIEW')
order by table_name
open s
fetch next from s into @name, @schema 
while @@fetch_status = 0
begin
 declare c cursor fast_forward for 
 select quotename(column_name) as column_name
 from information_schema.columns (nolock)
 where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name  = @name and TABLE_SCHEMA = @schema
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   print 'Processing table - ' + @name + ' schema - '+@schema+', column - ' + @column
   exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column + ','''+@schema+''' from [' + @schema+'].'+@name + ' where ' + @column + ' like ''' + @substr + '''')
   fetch next from c into @column
 end
 close c
 deallocate c
 fetch next from s into @name, @schema 
end
close s
deallocate s

select * from #rslt
drop table #rslt

Некоторые полезные преобразования


set nocount on

declare @d datetime

set @d = convert(char(8), getdate(), 112)

select 'Дата ', @d

select 'первый день месяца',dateadd(day,1-day(convert(char(8),getdate(),112)),convert(char(8),getdate(),112))

select  'последний день месяца',dateadd(month,1,dateadd(day,1-day(@d),@d))-1

select 'первый день года',dateadd(day,1-datepart(dayofyear,@d),@d),convert(datetime,'1/1/'+convert(char(4),year(@d)),101)


select 'последний день года',convert(datetime,'12/31/'+convert(char(4),year(@d)),101)

select 'первый день квартала',convert(datetime,convert(varchar(2),(month(@d)-1)/3*3+1)+'/1/'+convert(char(4),year(@d)),101),convert(datetime,convert(varchar(2),convert(varchar(2),(datepart(quarter,@d)-1)*3)+1)+'/1/'+convert(char(4),year(@d)),101)

select 'последний день квартала',dateadd(month,3,convert(datetime,convert(varchar(2),(month(@d)-1)/3*3+1)+'/1/'+convert(char(4),year(@d)),101))-1 print 'Русская нумерация дней недели'


SET DATEFIRST 1

select datepart(weekday, getdate())

Удаление записей из большой таблицы

Чтобы не сильно разрастался файл журнала транзакций, надо из таблицы удалять записи порциями.

declare @kol bigint,  @i bigint
set @kol = 20000
set @i = 0
set nocount on
set rowcount @kol
while (exists (select * from FirewallLog (nolock)
               where logTime < dateadd(month, -3, getdate())))
begin
	delete from FirewallLog	where logTime < dateadd(month, -3, getdate())
        set @i = @i + @kol
	if @i % 100000 = 0
        begin
            print 'Удаленных записей = '+cast(@i as varchar(20))
        end
end
set rowcount 0
set nocount off

еще вариант

dbcc traceon (610, -1)
set nocount on
declare @i bigint, @j bigint
set @i = 0
set @j = 10000
declare @date datetime
set @date = dateadd(month, -3, getdate()) 

while 1 = 1 
begin
  delete top(@j)
  FROM UserFields15
  where f20_creation < @date
    
  if @@ROWCOUNT = 0   
    break  
  else
  begin  
    set @i = @i + @j
    print 'Удаленных записей = '+cast(@i as varchar(20))
  end
end

set nocount off
dbcc traceoff (610, -1)