It happens sometimes, how do i get to know when my stored procedure dropped/removed without my knowledge?
它有时会发生,我怎么知道我的存储过程何时在我不知情的情况下丢弃/删除?
It annoys me whenever I debugs and found to know that the stored procedure doesn't exits, which created and tested some days ago.
每当我调试并发现知道存储过程没有退出时,它会让我很烦,这是几天前创建和测试的。
Is there a way to know the removed Stored Procedures in MS SQL Server?
有没有办法知道MS SQL Server中删除的存储过程?
6 个解决方案
#1
The standard way to check if procedure exists is
检查程序是否存在的标准方法是
if exists(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = N'PROCEDURE' and routine_name = @procname)
print 'exists'
Starting with MSSQL 2005 you can use [DDL trigger](http://msdn.microsoft.com/en-us/library/ms190989(SQL.90%29.aspx) to send email notification when procedure is dropped or created:
从MSSQL 2005开始,您可以使用[DDL trigger](http://msdn.microsoft.com/en-us/library/ms190989(SQL.90%29.aspx)在删除或创建过程时发送电子邮件通知:
USE msdb
GO
CREATE TABLE ddl_log
(ID int idenity(1,1) PRIMARY KEY CLUSTERED,
PostTime datetime,
DB_User nvarchar(100),
Event nvarchar(100),
TSQL nvarchar(2000));
CREATE TRIGGER DDL_Notify
ON DATABASE
FOR DROP_PROCEDURE, CREATE_PROCEDURE
AS
DECLARE @data XML,
@tableHTML NVARCHAR(MAX) ;
SET @data = EVENTDATA()
INSERT msdb.dbo.ddl_log (PostTime, DB_User, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), USER_NAME()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
SET @tableHTML =
N'<H1>DDL Table Event</H1>' +
N'<table border="1">' +
N'<tr><th>Post Time</th><th>User</th>' +
N'<th>TSQL</th><th></tr>' +
CAST ( ( SELECT td = PostTime, '',
td = DB_User, '',
td = TSQL, ''
FROM msdb.dbo.ddl_log
WHERE id = (select max(id) from msdb.dbo.ddl_log)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'dba@youraddress.com',
@subject = 'DDL Table Event',
@body = @tableHTML,
@body_format = 'HTML'
#2
If you want to be more or less database-vendor independent, you can use the SQL Standard catalog views called INFORMATION_SCHEMA - they're implemented in SQL Server, MySQL and many other system:
如果您希望或多或少地与数据库供应商无关,则可以使用名为INFORMATION_SCHEMA的SQL标准目录视图 - 它们在SQL Server,MySQL和许多其他系统中实现:
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME = 'YourStoredProcName'
If you get back a row, the stored proc is still in the system - if not, it's gone.
如果你回到一行,存储过程仍然在系统中 - 如果没有,它就消失了。
Marc
#3
if not exists(select * from sysobjects where type='P' and name= @procedure_name)
print 'does not exist'
#4
The above answers are correct, but I never understand why seasoned dba's don't include the schema name. Below shows SCHEMA and case sensitive corrections.
上面的答案是正确的,但我不明白为什么经验丰富的dba不包括模式名称。下面显示了SCHEMA和区分大小写的更正。
IF NOT EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspMyProcedure'
)
BEGIN
print 'Does not exist'
END
..
Schemas Discussion on Microsoft.com
Microsoft.com上的架构讨论
http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx
..
As far as best practices on "if exists" ... I would also go through Andy Leonard's 5 part series (and growing) on Test-Driven development with TSQL.
至于“如果存在”的最佳实践......我还将通过TSQL通过Andy Leonard的5部分系列(以及不断增长的)进行测试驱动开发。
#5
Quick and dirty way would be to do:
快速而肮脏的方式是:
sp_helptext proc_name
If the function complains then the stored procedure is missing.
如果函数抱怨,则缺少存储过程。
If you want to find out programmatically then perform a query on the sysobjects table.
如果要以编程方式查找,请在sysobjects表上执行查询。
#6
If you really want to know "when" your procedure is removed, look up DDL triggers (SQL 2005 and up).
如果您真的想知道“何时”删除了您的过程,请查找DDL触发器(SQL 2005及更高版本)。
#1
The standard way to check if procedure exists is
检查程序是否存在的标准方法是
if exists(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = N'PROCEDURE' and routine_name = @procname)
print 'exists'
Starting with MSSQL 2005 you can use [DDL trigger](http://msdn.microsoft.com/en-us/library/ms190989(SQL.90%29.aspx) to send email notification when procedure is dropped or created:
从MSSQL 2005开始,您可以使用[DDL trigger](http://msdn.microsoft.com/en-us/library/ms190989(SQL.90%29.aspx)在删除或创建过程时发送电子邮件通知:
USE msdb
GO
CREATE TABLE ddl_log
(ID int idenity(1,1) PRIMARY KEY CLUSTERED,
PostTime datetime,
DB_User nvarchar(100),
Event nvarchar(100),
TSQL nvarchar(2000));
CREATE TRIGGER DDL_Notify
ON DATABASE
FOR DROP_PROCEDURE, CREATE_PROCEDURE
AS
DECLARE @data XML,
@tableHTML NVARCHAR(MAX) ;
SET @data = EVENTDATA()
INSERT msdb.dbo.ddl_log (PostTime, DB_User, Event, TSQL)
VALUES (GETDATE(), CONVERT(nvarchar(100), USER_NAME()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
SET @tableHTML =
N'<H1>DDL Table Event</H1>' +
N'<table border="1">' +
N'<tr><th>Post Time</th><th>User</th>' +
N'<th>TSQL</th><th></tr>' +
CAST ( ( SELECT td = PostTime, '',
td = DB_User, '',
td = TSQL, ''
FROM msdb.dbo.ddl_log
WHERE id = (select max(id) from msdb.dbo.ddl_log)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'dba@youraddress.com',
@subject = 'DDL Table Event',
@body = @tableHTML,
@body_format = 'HTML'
#2
If you want to be more or less database-vendor independent, you can use the SQL Standard catalog views called INFORMATION_SCHEMA - they're implemented in SQL Server, MySQL and many other system:
如果您希望或多或少地与数据库供应商无关,则可以使用名为INFORMATION_SCHEMA的SQL标准目录视图 - 它们在SQL Server,MySQL和许多其他系统中实现:
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME = 'YourStoredProcName'
If you get back a row, the stored proc is still in the system - if not, it's gone.
如果你回到一行,存储过程仍然在系统中 - 如果没有,它就消失了。
Marc
#3
if not exists(select * from sysobjects where type='P' and name= @procedure_name)
print 'does not exist'
#4
The above answers are correct, but I never understand why seasoned dba's don't include the schema name. Below shows SCHEMA and case sensitive corrections.
上面的答案是正确的,但我不明白为什么经验丰富的dba不包括模式名称。下面显示了SCHEMA和区分大小写的更正。
IF NOT EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspMyProcedure'
)
BEGIN
print 'Does not exist'
END
..
Schemas Discussion on Microsoft.com
Microsoft.com上的架构讨论
http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx
..
As far as best practices on "if exists" ... I would also go through Andy Leonard's 5 part series (and growing) on Test-Driven development with TSQL.
至于“如果存在”的最佳实践......我还将通过TSQL通过Andy Leonard的5部分系列(以及不断增长的)进行测试驱动开发。
#5
Quick and dirty way would be to do:
快速而肮脏的方式是:
sp_helptext proc_name
If the function complains then the stored procedure is missing.
如果函数抱怨,则缺少存储过程。
If you want to find out programmatically then perform a query on the sysobjects table.
如果要以编程方式查找,请在sysobjects表上执行查询。
#6
If you really want to know "when" your procedure is removed, look up DDL triggers (SQL 2005 and up).
如果您真的想知道“何时”删除了您的过程,请查找DDL触发器(SQL 2005及更高版本)。