Обработка результатов выборки select в цикле: Отключение отслеживания изменения всех таблиц БД

/*
Данные скрипт отключает отслеживание изменения всех таблиц для БД DBName
Выборка, значения которой будут обрабатываться
              SELECT OBJECT_NAME([object_id]) 
              FROM [DBName].[sys].[change_tracking_tables]
Переменная цикла 
@id
Вместо DBName надо вписать свое наименование базы данных
*/

declare @id_cursor cursor
declare @id varchar(64) /*Переменная для хранения обрабатываемого значения выборки*/
declare @sql varchar(256);

set @id_cursor =  cursor local dynamic for
              SELECT OBJECT_NAME([object_id]) 
              FROM [DBName].[sys].[change_tracking_tables]

open   @id_cursor
fetch next from @id_cursor into @id
if (@@fetch_status = 0)
   while (@@fetch_status = 0)
   begin
         set @sql='alter table ['+ @id + '] Disable CHANGE_TRACKING;'
         exec (@sql) 
        fetch next from @id_cursor into @id
   end
close      @id_cursor
deallocate @id_cursor

Отслеживаем изменения в таблице с помощью триггеров Transact Sql

Задача: сохранять информацию о всех изменения в определенной таблицы. Наиболее простое решение - создать таблицу, совпадающую по структуре с отслеживаемой и при каждом изменении писать в эту таблицу строку с обновленным состоянием измененной строки. Сделать это можно с помощью триггера, отслеживающего операции INSERT, UPDATE и DELETE.

Ниже приведен код триггера для исходной таблицы людей PERSONS таблицы изменений CHANGES_PERSONS вида:

CREATE TABLE PERSONS

(

PERSON_ID INT NOT NULL IDENTITY PRIMARY KEY,

SURNAME VARCHAR(150) NOT NULL,

NAME VARCHAR(150) NOT NULL,

OTCHESTVO VARCHAR(150) NOT NULL

)

 

CREATE TABLE CHANGES_PERSONS

(

CHANGE_ID_PERS int not null identity,

CHANGE_DATE datetime not null default getdate(),

CHANGE_TYPE varchar(10) not null,

PERSON_ID INT NOT NULL,

SURNAME VARCHAR(150) NOT NULL,

NAME VARCHAR(150) NOT NULL,

OTCHESTVO VARCHAR(150) NOT NULL

)

view sourceprint?

--- ТРИГГЕР ОТСЛЕЖИВАЮЩИЙ ИЗМЕНЕНИЯ

create trigger dbo.changes_persons_trigger

on  PERSONS FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION

as

-- SET NOCOUNT ON добавлен чтобы не было лишних результатов выполнения операции

set NOCOUNT ON;

 

-- определеяем тип произошедших изменений INSERT,UPDATE, or DELETE

declare @change_type as varchar(10)

declare @count as int

set @change_type = 'inserted'

select @count = COUNT(*) FROM DELETED

if @count > 0

begin

    set @change_type = 'deleted'

    select @count = COUNT(*) from INSERTED

    if @Count > 0

        set @change_type = 'updated'

end

         

-- обработка удаления

if @change_type = 'deleted'

begin

    insert into CHANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'deleted', PERSON_ID, SURNAME, NAME, OTCHESTVO from deleted

end

else

begin

-- триггер не различает вставку и удаление, так что добавим ручную обработку

-- обработка вставки

    if @change_type = 'inserted'

    begin

        insert into HANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'inserted', PERSON_ID, SURNAME, NAME, OTCHESTVO from inserted

    end

-- обработка обновления

    else

    begin

        insert into HANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'updates', PERSON_ID, SURNAME, NAME, OTCHESTVO from inserted

    end

end -- завершение if

-- завершение dbo.changes_persons

Ссылка на источник

Поиск в таблицах БД строки версия 2

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = 'Здесь вставить искомую строку'

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

-- Purpose: To search all columns of all tables for a given search string

-- Written by: Narayana Vyas Kondreddi

-- Site: http://vyaskn.tripod.com

-- Updated and tested by Tim Gaunt

-- http://www.thesitedoctor.co.uk

-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010

-- Date modified: 03rd March 2011 19:00 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

    SET @ColumnName = ''

    SET @TableName = 

    (

        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

        FROM     INFORMATION_SCHEMA.TABLES

        WHERE         TABLE_TYPE = 'BASE TABLE'

            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

            AND    OBJECTPROPERTY(

                    OBJECT_ID(

                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                         ), 'IsMSShipped'

                           ) = 0

    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

         

    BEGIN

        SET @ColumnName =

        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))

            FROM     INFORMATION_SCHEMA.COLUMNS

            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

                AND    TABLE_NAME    = PARSENAME(@TableName, 1)

                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'uniqueidentifier')

                AND    QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

         

        BEGIN

            INSERT INTO #Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

            )

        END

    END   

END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Ссылка на источник

Поиск в таблицах БД строки

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
declare @sql nvarchar(max),
@newval nvarchar(max)

set @substr = N'Bug' --фрагмент строки, который будем искать
set @sql = N''
set @newval = N'Ошибка'

create table #rslt
(table_name varchar(128), field_name varchar(128), value nvarchar(max))

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

select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Matches] from #rslt
group by table_name, field_name
order by table_name, field_name
--Если нужно, можем отобразить все найденные значения
--select * from #rslt order by table_name, field_name


select
@sql = @sql + N'UPDATE dbo.'+table_name+N' SET '+field_name+N' = N''' + @newval+N''' WHERE '+ field_name +N' = N'''+@substr+N''''+CHAR(13)
from #rslt WHERE field_name <> N'[String]'
group by table_name, field_name
order by table_name, field_name


print @sql

drop table #rslt

close s
deallocate s



Ссылка на источник

Урезание таблицы БД при превышении ограничения на размер БД

Нередко случалось так, что в MSSQL Express БД превышала установленнное для нее ограничение (для MSSQL Express 2005, например, в 4 Гб). При этом не было смысла делать шринк базы так как в файле БД не было свободного места, а журнал транзакций был пуст (модель восстановления Simple). Но можно было почистить какую-либо таблицу. Но так как база превышала ограничение на размер, чистка также не увенчалась успехом так как при удалении строк из таблицы (delete from table) SQL server не мог записать в файл транзакций транзакции из-за превышения размера базы. Путем поисков в интернете был найден следующий небольшой скрипт, который после его выполнения частично отключает запись в лог транзакций:
sp_dboption MyDB, 'trunc. log on chkpt', TRUE

Теперь можно на таблице выполнить удаление строк:


delete from table

И запустить следующий скрипт, который включает запись в лог транзакций:

sp_dboption MyDB, 'trunc. log on chkpt', FALSE

Данный метод не работает на MS SQL 2012