Собственно, это вопрос из двух частей.
Можно ли использовать какую-либо функциональность для поиска по каждой хранимой процедуре для строки и, возможно, ее замены, например, стандартной функции Find/Replace?
Если у вас есть весь код хранимой процедуры, введите полный путь к базе данных, такой как этот [db1].[dbo].[table1], и вы измените имя базы данных на [db2], есть ли способ для SQL Server автоматически обновлять весь код из [db1] таблицы в [db2]? Или это нужно сделать вручную?
Для поиска: если вам нужно найти объекты базы данных (например, таблицы, столбцы, триггеры) по имени - посмотрите на инструмент БЕСПЛАТНО Red-Gate, который называется SQL Search, который делает это - он ищет всю вашу базу данных для любых строк (строк).
Это отличный инструмент для любого разработчика базы данных или базы данных - я уже упоминал об этом абсолютно БЕСПЛАТНО для использования в любом виде?
Однако этот инструмент не поддерживает замену текста, но даже просто найти все соответствующие хранимые процедуры (или другие объекты БД) очень полезно!
В окне "Подробности обозревателя объектов" в SSMS откройте папку хранимых процедур. Выберите все объекты (вы можете выбрать несколько объектов в этом окне, что является практически единственной целью окна "Подробности обозревателя объектов") и щелкните правой кнопкой мыши, выбрав для сценария DROP и CREATE. Теперь вы можете выполнить поиск/замену, заменив все, что вам нужно, за один раз до его выполнения.
Изменение: я в блоге об этом решении.
Поздно, но, надеюсь, полезно.
Существует бесплатный инструмент поиска от ApexSQL, который может находить и переименовывать объекты в базе данных.
Говорят, что у него есть опция умного переименования, которая найдет/заменит все вхождения некоторого объекта, такого как таблица, функция или хранимая процедура.
Я должен добавить, что я не использовал функцию переименования, но могу подтвердить, что поиск работает достаточно хорошо.
Также я не связан с ApexSQL, но я использую их инструменты.
Экспортировать все SP в файл. Используйте свой любимый инструмент для редактирования текста для поиска/замены. Обновите базу данных, выполнив script (пока вы не переименовываете процедуры).
Если вы явно определяете полный путь к базе данных, вам необходимо вручную (см. выше) обновить хранимые процедуры. Если вы не включаете имя базы данных или используете связанный сервер или аналогичный, изменений не требуется.
Сохраненные процедуры не могут быть обновлены на месте, не выписывая их в качестве операторов ALTER PROCEDURE (или DROP/CREATE, но я предпочитаю ALTER PROCEDURE.. больше об этом мгновенно). Хорошей новостью является то, что вы можете script выполнить все процедуры в одном файле через SSMS. Вначале операторы DDL будут CREATE PROCEDURE, которые вы захотите заменить на ALTER PROCEDURE вместе с другими изменениями.
В то время как вы могли альтернативно script процедуры как DROP/CREATE, мне не нравится делать это для большого количества скриптов, потому что он вызывает ошибки зависимостей.
Что касается второй части вашего вопроса, вам необходимо вручную отредактировать любые изменения пути к базе данных через script.
Вы можете искать текст определений хранимых процедур, используя этот
SELECT
Name
FROM
sys.procedures
WHERE
OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourSearchText%'
Замена, как правило, плохая идея, так как вы не знаете контекст текста, который вы найдете в хранимых процедурах. Вероятно, это возможно, хотя через скрипты Powershell.
Я предпочитаю это решение для любых других, так как мне удобно писать запросы, поэтому поиск текста во всех хранимых процедурах, которые находятся в схеме (x) и базе данных (y), и имена, начинающиеся с (z), являются довольно простой и интуитивно понятный запрос.
Я нашел этот 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
Я просто запускаю этот код, чтобы найти конкретный текст во всех хранимых процедурах:
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 '\';
Невозможно сделать это со встроенными функциями. Хотя это не поможет вам сегодня, я бы предложил изменить все ваши ссылки на синонимы, пока вы там, Таким образом, когда это произойдет снова в будущем (и это произойдет снова), все ваши внешние ссылки находятся в одном месте и легко обновляются. Кстати, у меня есть сообщение в блоге на последнем.
Если у вас есть время простоя.
Перейдите в "Сгенерировать скрипты" и сгенерируйте скрипты "create" для всех ваших sprocs, которые вы хотите отредактировать.
Замените текст в script и просто запустите его и заново создайте.
Вот один, который я написал сегодня, чтобы помочь с проектом обновления сервера.
Выполняет поиск всех сохраненных процедур и представлений во всех пользовательских базах данных на сервере и автоматически заменяет строку поиска другой. Идеально подходит для изменения жестко закодированных связанных имен серверов и тому подобного:
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 и может быть расширен для обработки различных типов функций.
Будьте осторожны, хотя! С большой властью приходит большая ответственность...
Обновление Я только что понял, что ссылка в ответе Дэвида включает функцию поиска. опять же, это отличный ответ.
Ответ Дэвида Аткинсона великолепен, просто хочу добавить поисковую часть. (не уверен, когда поиск будет добавлен в SSMS, моя версия SSMS V17.9.1)
Вместо того, чтобы выбирать хранимую процедуру по одному, я могу сделать поиск.
Хм, отбросив и переработав все обработанные процедуры, к сожалению, он разбил SQL-сервер, на который полагался SCADA для довольно большого factory.
Он сохранил немного усилий, редактируя их индивидуально, и factory был остановлен только после перезагрузки сервера.
Но проявляйте осторожность. Я на мгновение остался на месте.