MSSQL search and replace value in all tables

It's little script for replace value in database

declare @from as nvarchar(50);
declare @to as nvarchar(50);

set @from  =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7'); 
set @to  =[dbo].[getstringUUIDFromString] ('c3a5fb3e-745a-11e8-a209-0050568411f7'); 



DECLARE @CURSOR CURSOR;DECLARE @script AS NVARCHAR(MAX);
SET @CURSOR = CURSOR SCROLL
FOR
 SELECT 
 '
 if exists (select top 1 * from  ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+'] where '+isc.COLUMN_NAME + ' in ('+@from+'))
 begin
 print ''['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']'';

 
 update ['+isc.TABLE_CATALOG+'].['+isc.TABLE_SCHEMA+'].['+isc.TABLE_NAME+']
 set '+isc.COLUMN_NAME + ' = '+@to+'
 where '+isc.COLUMN_NAME + ' in ('+@from+')
end;
 '
FROM information_schema.columns  isc
where  TABLE_CATALOG='DiachokERP' and TABLE_SCHEMA='dbo'
and data_type = 'binary' and CHARACTER_MAXIMUM_LENGTH=16

OPEN @CURSOR

FETCH NEXT
FROM @CURSOR
INTO @script
 

WHILE @@FETCH_STATUS = 0
BEGIN

 EXEC(@script);
 --print @script
 FETCH NEXT
 FROM @CURSOR
 INTO @script
END
CLOSE @CURSOR
DEALLOCATE @CURSOR;

and converting string from uid to uuid

CREATE function [dbo].[getstringUUIDFromString](@stringUUID as varchar(50))
returns nvarchar(50)
as
begin
--Возврат Сред(GUID, 20, 4) + Прав(GUID, 12) + Сред(GUID, 15, 4) + Сред(GUID, 10, 4) + Лев(GUID, 8);
declare @buffer nvarchar(50)
select @buffer ='0x'+ substring(@stringUUID,20,4)+right(@stringUUID,12)+substring(@stringUUID,15,4)+substring(@stringUUID,10,4)+left(@stringUUID,8)

return @buffer
end

GO

Новый скрипт по реиндексации

Обновленный скрипт по реиндекации

use GlobalBase;
DECLARE @command nvarchar(4000); 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;
 
 SELECT      'BEGIN TRY ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;' AS [Инструкция T-SQL]
INTO #work_to_do
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('CLUSTERED INDEX')
AND   (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND   afp.page_count > 12
UNION ALL
SELECT      [Инструкция T-SQL] =
      CASE
            WHEN afp.avg_fragmentation_in_percent >= 15
            OR afp.avg_page_space_used_in_percent <= 60
            THEN 'BEGIN TRY ALTER INDEX [' + i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE = ON) END TRY BEGIN CATCH ALTER INDEX ['+ i.name + '] ON [' +OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (ONLINE=OFF) END CATCH;'
            WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
            OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
      END
FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN  sys.indexes AS i
ON    (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND   afp.database_id = DB_ID()
AND   afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND   (
            (afp.avg_fragmentation_in_percent >= 10 AND    afp.avg_fragmentation_in_percent < 15)
      OR    (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
      )
AND   afp.page_count > 12
AND   afp.OBJECT_ID NOT IN    (    
                             SELECT      OBJECT_ID
                             FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')
                             WHERE database_id = DB_ID()
                             AND   index_type_desc IN ('CLUSTERED INDEX')
                             AND   (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
                             AND   page_count > 1
                        )
--ORDER BY [Инструкция T-SQL]

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @command;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        print ''''+(@command)+'''';
        exec(@command)
        

        FETCH NEXT
           FROM partitions
           INTO @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

Удаление устаревших данных с 1С за определенный период

Иногда просят почистить базу 1С за определенный период, стандартными методами это долгий и хлопотный процесс, используя же mssql получаем быстро и комфортно.

use work_TC_Digma;
Declare @Instruction as varchar(MAX) 
declare @DateB as varchar(MAX);
set @DateB='4012-01-01 00:00:00.000'
Declare TablesAndFields cursor for 
SELECT objects.name as Tablename, isnull(objects1.name,'') as Tablename1, columns.name as columnname 
FROM 
   dbo.sysobjects as objects 
left join dbo.syscolumns as columns 
    on objects.id = columns.id
Left join dbo.sysobjects as objects1
    on objects1.name like objects.name+'%' 
where objects.xtype = 'U'
and columns.xtype = 61 and (columns.name='_Period' or columns.name='_Date_Time')
order by objects.name, objects1.name desc
 
open TablesAndFields
 
Declare @TableName as varchar(100) 
Declare @TableName1 as varchar(100) 
Declare @ColumnName as varchar(100) 
 
FETCH NEXT FROM TablesAndFields 
    into @TableName, @TableName1, @ColumnName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @TableName1 =@TableName
        set @Instruction = 'delete '+@TableName+' where '+@ColumnName+'<'''+@DateB+''''
    ELSE
        set @Instruction = 'delete '+@TableName1 +' where ' + @TableName+'_IDRRef in (select _IDRRef from '+@TableName+' where '+@ColumnName+'<'''+@DateB+''')'
    print @Instruction
    EXEC(@Instruction)
    

   FETCH NEXT FROM TablesAndFields
    into @TableName,@TableName1, @ColumnName
END
 
close TablesAndFields
deallocate TablesAndFields

Статья по анализу работы MSSQL

Очень правильная статья по анализу работы скула http://msdn.microsoft.com/ru-ru/magazine/cc135978.aspx