sql-server - salida - procedimiento almacenado sql server select

Recuperar mediante programación el origen del procedimiento almacenado de SQL Server que es idéntico al origen devuelto por la GUI de SQL Server Management Studio?

Question

¿Alguna sugerencia sobre cómo puedo obtener programáticamente el origen de procedimiento almacenado idéntico de SQL Server 2005, como cuando hago clic derecho en ese procedimiento almacenado en SQL Server Management Studio y selecciono modificar?

Estoy intentando usar SMO, pero hay algunas diferencias textuales. El procedimiento siempre tiene CREATE, no ALTER, y hay algunas diferencias en el encabezado, como la ausencia de GO en la versión que obtengo mediante programación. Puedo arreglar esto, pero tal vez hay una mejor manera?

De nuevo, estoy en SQL Server 2005, usando SMSE. Usando SMO a través de Visual Studio 8 2008.

Actualización : obtuve algunas respuestas que explican los aspectos básicos de cómo recuperar el procedimiento almacenado. Lo que estoy buscando es recuperar el texto idéntico (o casi idéntico) a lo que genera la GUI.

Ejemplo: para sp_mysp, haga clic con el botón derecho en Management Studio, seleccione modificar. Esto genera:

USE [MY_DB] GO /****** Object: StoredProcedure [dbo].[sp_mysp] Script Date: 01/21/2009 17:43:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[sp_mysp]

Me gustaría obtener programáticamente lo mismo (observe los GO en el encabezado, y el hecho de que es un PROCEDIMIENTO DE ALTERACIÓN. Idealmente, me gustaría obtener esto con una mínima reparación programática de la fuente recuperada.

Me encantaría obtener solo algo que difiera en los detalles de la fecha del script. . .

Answer 1

Dijiste programáticamente, ¿verdad? Espero que C # esté bien. Sé que dijiste que probaste SMO y no hizo exactamente lo que querías, por lo que probablemente no sea perfecto para tu solicitud, pero leerá programáticamente las declaraciones de SQL legítimas que podrías ejecutar para recrear el procedimiento almacenado. Si no tiene las instrucciones GO que desea, probablemente pueda suponer que cada una de las cadenas en StringCollection podría tener un GO después de ella. Puede que no obtengas ese comentario con la fecha y la hora en él, pero en mi proyecto de sonido similar (herramienta de implementación de gran culo que tiene que respaldar todo de forma individual), esto ha funcionado bastante bien. Si tiene una base previa de la que quería trabajar, y todavía tiene la base de datos original para ejecutar esto, consideraría lanzar el esfuerzo inicial y volver a estandarizar esta salida.

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;

Answer 2

El Asistente de publicación de Databse puede volcar el esquema (y otros objetos) desde la línea de comando.

Answer 3

Estoy de acuerdo con Mark. Configuré la salida en modo texto y luego sp_HelpText ''sproc''. Tengo esto vinculado a Crtl-F1 para hacerlo más fácil.

Answer 4

Para alterar un procedimiento almacenado, aquí está el código de C #:

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();

Cosas a tener en cuenta:

1. Asegúrese de que el USUARIO en la cadena de conexión tenga el derecho de alterar SP
2. Elimine todas las instrucciones GO,SET ANSI_NULLS XX,SET QUOTED_IDENTIFIER del archivo de script. (Si no lo hace, el comando Sql lanzará un error).

Answer 5

Solo quiero señalar que, en lugar de utilizar Buscar y reemplazar para cambiar el procedimiento de creación para modificar el procedimiento, es mejor usar una gota, puede colocarlo en la parte superior y requiere una búsqueda de texto.

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

Si está seguro de que está allí, creo que podría simplemente dejarlo también, pero no lo recomendaría. No olvide ir, ya que el procedimiento de creación debe ser la primera y única declaración en un lote.

O el enfoque perezoso:

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

Answer 6

Tendrás que codificarlo a mano, el Analizador de SQL revela lo siguiente.

SMSE ejecuta una larga cadena de consultas cuando genera la instrucción.

La siguiente consulta (o algo similar) se usa para extraer el texto:

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'')

Devuelve el CREATE puro que luego se sustituye con ALTER en el código en alguna parte.

Las cosas SET ANSI NULL y las declaraciones y fechas de GO se anteponen a esto.

Ve con sp_helptext, es más simple ...

Answer 7

Use la siguiente instrucción select para obtener toda la definición completa:

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

supongo que SSMS y otras herramientas leen esto y hacen cambios cuando es necesario, como cambiar CREAR por ALTERAR. Por lo que sé SQL almacena otras representaciones del procedimiento

Answer 8

EXEC sp_helptext ''your procedure name'';

Esto evita el problema con el enfoque INFORMATION_SCHEMA en el que el procedimiento almacenado se corta si es demasiado largo.

Actualización: David escribe que esto no es idéntico a su sproc ... ¿quizás porque devuelve las líneas como ''registros'' para preservar el formato? Si desea ver los resultados en un formato más ''natural'', puede usar Ctrl-T primero (salida como texto) y debe imprimirlo exactamente como lo ingresó. Si está haciendo esto en código, es trivial hacer un foreach para armar sus resultados exactamente de la misma manera.

Actualización 2: Esto proporcionará a la fuente un "PROCEDIMIENTO DE CREACIÓN" en lugar de un "PROCEDIMIENTO DE ALTERACIÓN", pero no conozco ninguna forma de hacer que use "ALTER" en su lugar. Algo trivial, ¿no?

Actualización 3: consulte los comentarios para obtener más información sobre cómo mantener su SQL DDL (estructura de la base de datos) en un sistema de control de origen. Esa es realmente la clave de esta pregunta.