Самый быстрый/простой способ использования поиска/замены через все хранимые процедуры

Ответ 1

Для поиска: если вам нужно найти объекты базы данных (например, таблицы, столбцы, триггеры) по имени - посмотрите на инструмент БЕСПЛАТНО Red-Gate, который называется SQL Search, который делает это - он ищет всю вашу базу данных для любых строк (строк).

Это отличный инструмент для любого разработчика базы данных или базы данных - я уже упоминал об этом абсолютно БЕСПЛАТНО для использования в любом виде?

Однако этот инструмент не поддерживает замену текста, но даже просто найти все соответствующие хранимые процедуры (или другие объекты БД) очень полезно!

Ответ 2

В окне "Подробности обозревателя объектов" в SSMS откройте папку хранимых процедур. Выберите все объекты (вы можете выбрать несколько объектов в этом окне, что является практически единственной целью окна "Подробности обозревателя объектов") и щелкните правой кнопкой мыши, выбрав для сценария DROP и CREATE. Теперь вы можете выполнить поиск/замену, заменив все, что вам нужно, за один раз до его выполнения.

Изменение: я в блоге об этом решении.

Ответ 3

Поздно, но, надеюсь, полезно.

Существует бесплатный инструмент поиска от ApexSQL, который может находить и переименовывать объекты в базе данных.

Говорят, что у него есть опция умного переименования, которая найдет/заменит все вхождения некоторого объекта, такого как таблица, функция или хранимая процедура.

Я должен добавить, что я не использовал функцию переименования, но могу подтвердить, что поиск работает достаточно хорошо.

Также я не связан с ApexSQL, но я использую их инструменты.

Ответ 4

• Экспортировать все SP в файл. Используйте свой любимый инструмент для редактирования текста для поиска/замены. Обновите базу данных, выполнив script (пока вы не переименовываете процедуры).

• Если вы явно определяете полный путь к базе данных, вам необходимо вручную (см. выше) обновить хранимые процедуры. Если вы не включаете имя базы данных или используете связанный сервер или аналогичный, изменений не требуется.

Ответ 5

Сохраненные процедуры не могут быть обновлены на месте, не выписывая их в качестве операторов ALTER PROCEDURE (или DROP/CREATE, но я предпочитаю ALTER PROCEDURE.. больше об этом мгновенно). Хорошей новостью является то, что вы можете script выполнить все процедуры в одном файле через SSMS. Вначале операторы DDL будут CREATE PROCEDURE, которые вы захотите заменить на ALTER PROCEDURE вместе с другими изменениями.

В то время как вы могли альтернативно script процедуры как DROP/CREATE, мне не нравится делать это для большого количества скриптов, потому что он вызывает ошибки зависимостей.

Что касается второй части вашего вопроса, вам необходимо вручную отредактировать любые изменения пути к базе данных через script.

Ответ 6

Вы можете искать текст определений хранимых процедур, используя этот

SELECT 
  Name 
FROM 
  sys.procedures 
WHERE 
   OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourSearchText%'

Замена, как правило, плохая идея, так как вы не знаете контекст текста, который вы найдете в хранимых процедурах. Вероятно, это возможно, хотя через скрипты Powershell.

Я предпочитаю это решение для любых других, так как мне удобно писать запросы, поэтому поиск текста во всех хранимых процедурах, которые находятся в схеме (x) и базе данных (y), и имена, начинающиеся с (z), являются довольно простой и интуитивно понятный запрос.

Ответ 7

Я нашел этот script, где вы можете определить поиск и замену текстом и просто запустить его, чтобы сразу заменить текст во всех процедурах. Надеюсь, это поможет вам навалом.

-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON

DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)

-- text to search for
SET @searchFor = '[MY-SERVER]'
-- text to replace with
SET @replaceWith = '[MY-SERVER2]'

-- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX))

DECLARE curHelp CURSOR FAST_FORWARD
FOR
-- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string
SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ' 
FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

OPEN curHelp

FETCH next FROM curHelp INTO @sqlToRun

WHILE @@FETCH_STATUS = 0
BEGIN
   --insert stored procedure text into a temporary table
   INSERT INTO @temp
   EXEC (@sqlToRun)

   -- add GO after each stored procedure
   INSERT INTO @temp
   VALUES ('GO')

   FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE curHelp
DEALLOCATE curHelp

-- find and replace search string in stored procedures 
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)

SELECT spText FROM @temp
-- now copy and paste result into new window
-- then make sure everything looks good and run
GO

Вот ссылка:
http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures

Ответ 8

Я просто запускаю этот код, чтобы найти конкретный текст во всех хранимых процедурах:

SELECT DISTINCT 
   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 '%textToFind%'
   or m.definition Like '%\[ifTextIsAColNameWithBrackets\]%' ESCAPE '\';

Ответ 9

Невозможно сделать это со встроенными функциями. Хотя это не поможет вам сегодня, я бы предложил изменить все ваши ссылки на синонимы, пока вы там, Таким образом, когда это произойдет снова в будущем (и это произойдет снова), все ваши внешние ссылки находятся в одном месте и легко обновляются. Кстати, у меня есть сообщение в блоге на последнем.

Ответ 10

Если у вас есть время простоя.

Перейдите в "Сгенерировать скрипты" и сгенерируйте скрипты "create" для всех ваших sprocs, которые вы хотите отредактировать.

Замените текст в script и просто запустите его и заново создайте.

Ответ 11

Вот один, который я написал сегодня, чтобы помочь с проектом обновления сервера.

Выполняет поиск всех сохраненных процедур и представлений во всех пользовательских базах данных на сервере и автоматически заменяет строку поиска другой. Идеально подходит для изменения жестко закодированных связанных имен серверов и тому подобного:

set nocount on

if OBJECT_ID('tempdb..#dbs') is not null
    drop table #dbs

if OBJECT_ID('tempdb..#objects') is not null
    drop table #objects

declare @find as nvarchar(128) = 'Monkey'
declare @replace as nvarchar(128) = 'Chimp'

declare @SQL as nvarchar(max)
declare @current_db as sysname
declare @current_schema as sysname
declare @current_object as sysname
declare @current_type as char(2)
declare @current_ansi as bit
declare @current_quot as bit
declare @fullname as sysname
declare @preamble as nvarchar(128)

create table #objects
    (
     dbname sysname, 
     schemaname sysname, 
     objname sysname,
     objtype char(2),
     ansinulls bit,
     quotedidentifier bit
     )
create unique clustered index i on #objects (dbname, schemaname, objname)

select [name] into #dbs 
from master.sys.databases
where [name] not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB', 'SSISDB')


declare db_cursor cursor for select [name] from #dbs order by [name]
open db_cursor
fetch next from db_cursor into @current_db

while @@FETCH_STATUS = 0
begin
    set @SQL = 'insert into #objects select ''' + @current_db + ''', s.[name], o.[name], o.[type], m.uses_ansi_nulls, m.uses_quoted_identifier from ' + @current_db + '.sys.sql_modules as m '
             + 'join ' + @current_db + '.sys.objects AS o ON m.object_id = o.object_id '
             + 'join ' + @current_db + '.sys.schemas AS s ON o.schema_id = s.schema_id '
             + 'where m.definition like ''%' + @find + '%'' and type in (''P'', ''V'') and is_ms_shipped = 0 order by s.[name], o.[name]'

    exec sp_executeSQL @SQL
    fetch next from db_cursor into @current_db
end
close db_cursor
deallocate db_cursor



declare obj_cursor cursor for select dbname, schemaname, objname, objtype, ansinulls, quotedidentifier from #objects order by dbname, objname
open obj_cursor
fetch next from obj_cursor into @current_db, @current_schema, @current_object, @current_type, @current_ansi, @current_quot

while @@FETCH_STATUS = 0
begin
    set @fullname =  @current_db + '.' + @current_schema + '.' + @current_object

    set @preamble = CASE WHEN @current_ansi = 1 THEN 'SET ANSI_NULLS ON' ELSE 'SET ANSI_NULLS OFF' END + '; ' 
                  + CASE WHEN @current_quot = 1 THEN 'SET QUOTED_IDENTIFIER ON' ELSE 'SET QUOTED_IDENTIFIER OFF' END + '; ' 

    print 'Altering ' + @fullname 

    if @current_type = 'P'
    begin
        set @SQL = 'use ' + @current_db + '; ' + @preamble + 'declare @newproc nvarchar(max);'
                 + 'set @newproc = REPLACE(REPLACE(OBJECT_DEFINITION(OBJECT_ID(''' + @fullname + ''')), ''' + @find + ''', ''' + @replace + '''), ''CREATE PROCEDURE'', ''ALTER PROCEDURE''); '
                 + 'exec sp_executeSQL @newproc' 
        exec sp_executeSQL @SQL               
    end

    if @current_type = 'V'
    begin
        set @SQL = 'use ' + @current_db + '; ' + @preamble + 'declare @newproc nvarchar(max);'
                 + 'set @newproc = REPLACE(REPLACE(OBJECT_DEFINITION(OBJECT_ID(''' + @fullname + ''')), ''' + @find + ''', ''' + @replace + '''), ''CREATE VIEW'', ''ALTER VIEW''); '
                 + 'exec sp_executeSQL @newproc'        
        exec sp_executeSQL @SQL
    end
    fetch next from obj_cursor into @current_db, @current_schema, @current_object, @current_type, @current_ansi, @current_quot
end
close obj_cursor
deallocate obj_cursor

Он также обрабатывает уникальные настройки ANSI_NULL и QUOTED_IDENTIFIER и может быть расширен для обработки различных типов функций.

Будьте осторожны, хотя! С большой властью приходит большая ответственность...

Ответ 12

Обновление Я только что понял, что ссылка в ответе Дэвида включает функцию поиска. опять же, это отличный ответ.

Ответ Дэвида Аткинсона великолепен, просто хочу добавить поисковую часть. (не уверен, когда поиск будет добавлен в SSMS, моя версия SSMS V17.9.1)

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

• При поиске используется шаблон, похожий на "лайк" в TSQL

Ответ 13

Хм, отбросив и переработав все обработанные процедуры, к сожалению, он разбил SQL-сервер, на который полагался SCADA для довольно большого factory.

Он сохранил немного усилий, редактируя их индивидуально, и factory был остановлен только после перезагрузки сервера.

Но проявляйте осторожность. Я на мгновение остался на месте.