如何快速识别SQL Server中最近修改的存储过程

I Need to manually migrate modified stored procedures from a DEV SQL Server 2005 database instance to a TEST instance. Except for the changes I'm migrating, the databases have the same schemas. How can I quickly identify which stored procedures have been modified in the DEV database for migration to the TEST instance?

我需要手动将修改过的存储过程从一个DEV SQL Server 2005数据库实例迁移到一个测试实例。除了我正在迁移的更改之外，数据库具有相同的模式。如何快速确定在DEV数据库中修改了哪些存储过程以迁移到测试实例?

I assume I can write a query against some of the system tables to view database objects of type stored procedure, sorting by some sort of last modified or compiled data, but I'm not sure. Maybe there is some sort of free utility someone can point me to.

我假设我可以针对一些系统表编写查询，以查看存储过程类型的数据库对象，根据某种最后修改或编译的数据进行排序，但我不确定。也许有人可以给我一些免费的工具。

Thanks in advance,

提前谢谢,

Bob

鲍勃

7 个解决方案

#1

instead of using sysobjects which is not recommended anymore use sys.procedures

不再使用不推荐的sysobjects，而是使用syss .procedure

select name,create_date,modify_date
from sys.procedures
order by modify_date desc

you can do the where clause yourself but this will list it in order of modification date descending

您可以自己执行where子句，但这将按修改日期降序列出它

#2

Bob OMalley probably solved his problem long time ago but hopefully new readers will find this useful.

Bob OMalley很可能在很久以前就解决了这个问题，但是希望新的读者能发现这个有用的东西。

There are some special cases where scripts might not give optimal results.

在某些特殊情况下，脚本可能不能提供最佳结果。

One is deleting stored procedures or other objects in dev environment – you won’t catch this using system views because object won’t exist there any longer.

一个是在dev环境中删除存储过程或其他对象——您不会使用系统视图捕获它，因为对象将不再存在。

Also, I’m not really sure this approach can work on changes such as permissions and similar.

另外，我也不确定这种方法是否可以处理诸如权限之类的更改。

In such cases its best to use some third party tool just to double check nothing is missed.

在这种情况下，最好使用第三方工具来检查是否遗漏了什么。

I’ve successfully used ApexSQL Diff in the past for similar tasks and it worked really good on large databases with 1000+ objects but you can’t go wrong with SQL Compare that’s already mentioned here or basically any other tool that exists on the market.

我曾经成功地使用ApexSQL Diff来完成类似的任务，它在拥有1000多个对象的大型数据库上运行得非常好，但是这里已经提到的SQL Compare，或者市场上存在的其他工具，都不会出错。

Disclaimer: I’m not affiliated with any of the vendors I’m mentioning here but I do use both set of tools (Apex and RG) in the company I work for.

免责声明:我与这里提到的任何供应商都没有关联，但我在我工作的公司中使用了这两套工具(Apex和RG)。

#3

You can execute this query to find all stored procedures modified in the last x number of days:

您可以执行此查询，查找在最后x天内修改的所有存储过程:

SELECT name
FROM sys.objects
WHERE type = 'P'
    AND DATEDIFF(D,modify_date, GETDATE()) < X

#4

Although not free I have had good experience using Red-Gates SQL Compare tool. It worked for me in the past. They have a free trial available which may be good enough to solve your current issue.

虽然不是免费的，但我有很好的使用Red-Gates SQL比较工具的经验。它在过去对我很管用。他们有免费试用，可以解决你目前的问题。

#5

you can also use the following code snipet

您还可以使用以下代码snipet。

USE AdventureWorks2008;

GO

SELECT SprocName=name, create_date, modify_date

FROM sys.objects

WHERE type = 'P' 

AND name = 'uspUpdateEmployeeHireInfo'

GO

#6

You can use following type of query to find modified stored procedures , you can use any number then 7 as per your needs

您可以使用以下类型的查询来查找修改后的存储过程，您可以根据需要使用任何数字7

SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7

从系统选择的名字。对象类型= 'P'和DATEDIFF(D,modify_date, GETDATE()) < 7。

#7

There are several database compare tools out there. One that I've always like is SQLCompare by Red Gate.

有几个数据库比较工具。我一直喜欢的一个是SQLCompare by Red Gate。

You can also try using:

你也可以尝试使用:

SELECT name
FROM sys.objects
WHERE modify_date > @cutoffdate

In SQL 2000 that wouldn't have always worked, because using ALTER didn't update the date correctly, but in 2005 I believe that problem is fixed.

在SQL 2000中，这并不总是有效的，因为使用ALTER不能正确地更新日期，但是在2005年，我认为这个问题已经解决了。

I use a SQL compare tool myself though, so I can't vouch for that method 100%

我自己也使用SQL比较工具，所以我不能100%保证这个方法

#1