I was recently asked by a fellow DBA if I had "one of my scripts" to give a list of DTS package on a SQL Server 2000 instance. Since the majority of my work is in 2005 and 2008, I just ran the following query.
select * from msdb..sysdtspackages
The results were not helpful because most of the packages had multiple versions stored so instead of listing the 200+ packages, it listed the 7000+ versions of those packages. After a few minutes, I came up with the following query which I've now added to my script library.
SELECT
pkg.[name],
pkg.[owner],
pkg.[description],
cnt.[CreateDate],
cnt.[LastModified],
cnt.[NumOfVersions]
FROM
[msdb].[dbo].[sysdtspackages] pkg
INNER JOIN
(SELECT
[id],
COUNT(*) [NumOfVersions],
MIN([createdate]) [CreateDate],
MAX([createdate]) [LastModified]
FROM
[msdb].[dbo].[sysdtspackages]
GROUP BY
[id]) AS cnt
ON
pkg.id = cnt.id AND
pkg.createdate = cnt.LastModified
1 comment:
You can also use exec msdb..sp_enum_dtspackages which gives some of the same information. It does not give the original create date or the number of versions. It does give the size of the package.
Post a Comment