IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Test]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Sp_Test] GOUSE [Test] GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE PROCEDURE [dbo].[Sp_Test] AS BEGIN select 1 ENDGO
先判断是否存在,如果有就删除。 然后再create
用C#写个窗体程序好了,用sp_helptext获取存储过程,然后将第一行的create关键字改为alter,将文本放在textbox里面类似:form1放一个textbox(多行模式)、两个button代码:public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { } private void button1_Click(object sender, EventArgs e) { StringBuilder str = new StringBuilder(); int i = 0; using (SqlConnection con = new SqlConnection("server=.;user id=sa;pwd=QQ7391420QQ;database=test")) { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.CommandText = "sp_helptext"; cmd.Parameters.Add(new SqlParameter("@objname", "p_select")); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (i < 1) { str.Append(dr[0].ToString().Replace("create", "alter")); } else { str.Append(dr[0].ToString()); } i++; } dr.Close(); } textBox1.Text = str.ToString(); } private void button2_Click(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection("server=.;user id=sa;pwd=QQ7391420QQ;database=test")) { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.CommandText = textBox1.Text; cmd.ExecuteNonQuery(); } } } textbox1里面的存储过程可以添加一些检查机制,如存储过程结构检查等, 完善一下这个小实例,你就可以自己做个图形界面管理SQL信息了
declare @sp table(text nvarchar(255)) insert @sp exec sp_helptext 'xxxx' -- replace xxxx with your stored procedure name select replace(text, 'create', 'alter') from @sp
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Test]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Sp_Test]
GOUSE [Test]
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE [dbo].[Sp_Test]
AS
BEGIN
select 1
ENDGO
然后再create
{
public Form1()
{
InitializeComponent();
} private void Form1_Load(object sender, EventArgs e)
{ } private void button1_Click(object sender, EventArgs e)
{
StringBuilder str = new StringBuilder();
int i = 0;
using (SqlConnection con = new SqlConnection("server=.;user id=sa;pwd=QQ7391420QQ;database=test"))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.CommandText = "sp_helptext";
cmd.Parameters.Add(new SqlParameter("@objname", "p_select")); SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (i < 1)
{
str.Append(dr[0].ToString().Replace("create", "alter"));
}
else
{
str.Append(dr[0].ToString());
}
i++;
}
dr.Close();
}
textBox1.Text = str.ToString();
} private void button2_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("server=.;user id=sa;pwd=QQ7391420QQ;database=test"))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
cmd.CommandText = textBox1.Text;
cmd.ExecuteNonQuery();
}
}
}
textbox1里面的存储过程可以添加一些检查机制,如存储过程结构检查等,
完善一下这个小实例,你就可以自己做个图形界面管理SQL信息了
insert @sp exec sp_helptext 'xxxx' -- replace xxxx with your stored procedure name
select replace(text, 'create', 'alter') from @sp
如果你这样子会反存储过程里面的创建临时表的create也替换掉,我是想不用替换,有没有直接给我们调用的存储过程,如sp_helptext这样子的
如果你这样子会反存储过程里面的创建临时表的create也替换掉,我是想不用替换,有没有直接给我们调用的存储过程,如sp_helptext这样子的
declare @sp table(text nvarchar(255))
insert @sp select 'drop procedure xxxx' -- replace xxxx with your stored procedure name
insert @sp exec sp_helptext 'xxxx'
select text from @sp