Building SQL Server Inventory - Professional SQL Server 2008 Administration with Windows PowerShell - page 370

AS
DECLARE @ERRORCODE [int], @ERRMSG [varchar] (128)

-- If the database already exists in the inventory, then perform an update.
IF EXISTS (SELECT databaseID FROM [dbo].[Databases] WHERE serverID = @serverID and
databaseName = @databaseName)
BEGIN

UPDATE [dbo].[Databases]
SET [updateDate] = getDate()
WHERE serverID = @serverID and databaseName = @databaseName

END
-- If the database does not exist in the inventory, then perform an insertion.
ELSE
BEGIN

INSERT [dbo].[Databases]
( serverID
, databaseName
, createDate
, updateDate
)
VALUES(

@serverID
, @databaseName
, GETDATE()
, GETDATE()
)

END

SET @ERRORCODE = @@ERROR
IF @ERRORCODE <> 0
BEGIN
SET @ERRMSG = 'Upsert failed - ' + OBJECT_NAME(@@PROCID)
SET @ERRMSG = @ERRMSG + ' Error Code: ' +
RTRIM(CONVERT(CHAR, @ERRORCODE))
RAISERROR (@ERRMSG, 16, 1)
RETURN (-1)

END

GO

We now need a script, Upsert-Database.ps1 , to collect a list of databases on all our database servers.
This script connects to every server in our inventory, gets the list of user databases, and inserts them into
the inventory. The script is available for download from the Wrox website for this topic:

# ===========================================================
#
# NAME: Upsert-Database.ps1
#
# AUTHOR: Yan and MAK
# DATE : 6/8/2008
#
# COMMENT: This script collects all the user databases on all the servers and saves
them into inventory.
# ===========================================================