Как программно получить источник хранимой процедуры SQL Server, который идентичен источнику, возвращенному в SQL Server Management Studio gui?

Ответ 1

EXEC sp_helptext 'your procedure name';

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

Обновление: Дэвид пишет, что это не похоже на его sproc... возможно, потому что он возвращает строки как "записи" для сохранения форматирования? Если вы хотите увидеть результаты в более "естественном" формате, вы можете сначала использовать Ctrl-T (вывод в виде текста), и он должен распечатать его точно так же, как вы его ввели. Если вы делаете это в коде, тривиально делать foreach, чтобы собрать ваши результаты точно так же.

Обновление 2: это даст источнику "ПРОЦЕДУРА СОЗДАНИЯ", а не "ПРОЦЕДУРА ALTER", но я не знаю, как использовать его вместо "ALTER". Однако, может быть, тривиальная вещь не так ли?

Обновление 3: см. комментарии для получения дополнительной информации о том, как поддерживать SQL DDL (структуру базы данных) в системе управления версиями. Это действительно ключ к этому вопросу.

Ответ 2

Вам придется передать код, SQL Profiler обнаруживает следующее.

SMSE выполняет довольно длинную строку запросов, когда генерирует оператор.

Следующий запрос (или что-то в его строках) используется для извлечения текста:

SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=N'#test___________________________________________________________________________________________________________________00003EE1' and SCHEMA_NAME(sp.schema_id)=N'dbo')

Он возвращает чистый CREATE, который затем заменяется кодом ALTER.

Все атрибуты SET ANSI NULL и инструкции GO и даты добавляются к этому.

Пойдите с sp_helptext, его проще...

Ответ 3

Вы сказали программно, верно? Надеюсь, С# в порядке. Я знаю, что вы сказали, что вы пробовали SMO, и это не совсем так, как вам хотелось бы, так что это, вероятно, не будет идеальным для вашего запроса, но будет программным образом читать законные операторы SQL, которые вы могли бы запустить, чтобы воссоздать хранимую процедуру. Если у него нет операторов GO, которые вы хотите, вы можете предположить, что каждая из строк в StringCollection может иметь GO после него. Вы не можете получить этот комментарий с датой и временем в нем, но в моем похожем проекте (широкомасштабном инструменте развертывания, который должен выполнять резервное копирование всего по отдельности) это было сделано довольно красиво. Если у вас есть предварительная база, из которой вы хотите работать, и у вас все еще есть исходная база данных для ее запуска, я бы подумал о том, чтобы отбросить начальное усилие и рестайлинг на этом выходе.

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
…
string connectionString = … /* some connection string */;
ServerConnection sc = new ServerConnection(connectionString);
Server s = new Server(connection);
Database db = new Database(s, … /* database name */);
StoredProcedure sp = new StoredProcedure(db, … /* stored procedure name */);
StringCollection statements = sp.Script;

Ответ 4

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

select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES Where ROUTINE_NAME='someprocname'

Я предполагаю, что SSMS и другие инструменты читают это и вносят необходимые изменения, такие как изменение CREATE на ALTER. Насколько я знаю, SQL хранит не другие представления процедуры

Ответ 5

Я согласен с Марком. Я установил вывод в текстовый режим, а затем sp_HelpText 'sproc'. У меня это привязано к Crtl-F1, чтобы было легко.

Ответ 6

Databse Publishing Wizard может выгрузить схему (и другие объекты) из командной строки.

Ответ 7

Я просто хочу отметить, что вместо того, чтобы использовать find и replace для изменения, создайте процедуру для изменения процедуры, вы также можете использовать drop, вы можете поместить ее прямо вверху, и для этого требуется текстовый поиск.

IF exists (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'sp_name')
            and type in ('P','V') --procedure or view
        )
    DROP sp_name
GO

Если вы уверены в этом, я думаю, вы могли бы просто отказаться от него, но я бы не рекомендовал этого. Не забывайте идти, поскольку процедура создания должна быть первым и единственным оператором в пакете.

Или ленивый подход:

IF OBJECT_ID(N'sp_name') is not null
    DROP sp_name
GO

Ответ 8

Чтобы изменить хранимую процедуру, здесь код С#:

SqlConnection con = new SqlConnection("your connection string");
con.Open();
cmd.CommandType = System.Data.CommandType.Text;
string sql = File.ReadAllText(YUOR_SP_SCRIPT_FILENAME);
cmd.CommandText = sql;   
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();

Примечания:

• Убедитесь, что USER в строке подключения имеет право изменять SP
• Удалите все операторы GO,SET ANSI_NULLS XX,SET QUOTED_IDENTIFIER из файла script. (Если вы этого не сделаете, SqlCommand выдает ошибку).