Suche nach Objekten in Datenbanken
Aug 22nd
Bei Entwicklungen in größeren Umgebungen ist es häufig erforderlich, dass neben einer DEV-Umgebung noch eine TEST, UAT (User Acceptance Test) und ev. auch die Produktionsumgebung auf einem Server vorhanden ist. Eventuell ist es aber auch so, dass sie eine generische Entwicklung vorgenommen haben, die in allen Benutzerdatenbanken vorhanden sein sollte.
Mit Hilfe einer simplen Prozedur, die - wegen der allgemeinen Verfügbarkeit - in die Masterdatenbank implementiert wird, kann man relativ leicht Abhilfe schaffen. Der Prozedur übergeben Sie einfach das Objekt nebst Schema sowie den Objekttypen. Weiterhin übergeben Sie als Option, ob auch Systemdatenbanken (master, model, msdb, tempdb) durchsucht werden sollen. Als Ergebnis erhalten Sie eine Relation mit allen Datenbanken, in denen das Objekt gefunden wurde nebst Zeitstempel der Erstellung.
USE master
GO
IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID('sp_SearchObject', 'P'))
DROP PROC dbo.sp_SearchObject
GO
CREATE PROC dbo.sp_SearchObject
@ObjectName nvarchar(255),
@ObjectType nvarchar(10),
@InSystemDB bit
AS
SET NOCOUNT ON
-- needed variables
DECLARE @database sysname
DECLARE @ObjectId int
DECLARE @parms nvarchar(200)
DECLARE @sqlstmt nvarchar(1000)
-- Creation of temporary output object
CREATE TABLE #ResultList
(
[database] sysname NOT NULL,
[objectid] int NULL,
[objectname] nvarchar(255) NULL,
[objecttype] nvarchar(5) NULL,
[crdate] datetime NULL,
[refdate] datetime NULL,
)
-- definition of cursor for all affected databases
DECLARE c CURSOR LOCAL FORWARD_ONLY
FOR
SELECT name
FROM master.sys.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') OR
(
name IN ('master', 'model', 'msdb', 'tempdb') AND
@InSystemDB = 1
)
OPEN c
FETCH NEXT FROM c INTO @database
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @parms = N'@ObjectId int OUTPUT'
SET @sqlstmt = N'SET @ObjectId = OBJECT_ID(''[' + @database + '].' + @ObjectName + ''', ''' + @ObjectType + ''')'
EXEC sp_executeSQL @sqlstmt, @parms, @ObjectId = @ObjectId OUTPUT
SET @parms = N'@database sysname, @objectname nvarchar(255), @objecttype nvarchar(5), @objectid int'
SET @sqlstmt = N'INSERT INTO #ResultList
([database], [objectid], [objectname], [objecttype], [crdate], [refdate])
SELECT @database, id, @objectname, @objecttype, crdate, refdate
FROM [' + @database + '].sys.sysobjects
WHERE id = @objectid'
EXEC sp_executeSQL
@sqlstmt,
@parms,
@database = @database,
@objectname = @objectname,
@objecttype = @objecttype,
@objectId = @objectId
FETCH NEXT FROM c INTO @database
END
CLOSE c
DEALLOCATE c
ExitCode:
SELECT * FROM #ResultList
DROP TABLE #ResultList
SET NOCOUNT OFF
GO
Die nachfolgenden Beispiele zeigen die Verwendung der Prozedur:
EXEC sp_SearchObject @ObjectName = 'dbo.DTSStatus', @ObjectType = 'U', @InSystemDB = 0
Das obige Beispiel sucht nach einer Relation dbo.dtsstatus wobei die Systemdatenbanken ausgeschlossen werden während das nachfolgende Beispiel nach einer Systemprozedur sucht und dabei alle Systemdatenbanken berücksichtigt.
EXEC sp_SearchObject @ObjectName = 'dbo.sp_executeSQL', @ObjectType = 'X', @InSystemDB = 1
Weiterführende Informationen:
64390 Erzhausen, 23.08.2010
Uwe Ricken
db Berater GmbH
Verwendung von UDF auf Linked Server
Aug 20th
Die Verwendung von Linked Server ist eine tolle Sache; macht sie es mir doch leicht, ohne komplizierten erneuten Verbindungsaufbau unmittelbar von meinem Datenbankserver auf Datenbanken anderer Serversysteme zuzugreifen. Die nachfolgenden Codings beschränken sich auf den Zugriff auf SQL Server als Linked Server.
Grundsätzlich lassen sich Objekte, die sich auf einem Linked Server befinden, relativ einfach ansprechen. Sie müssen lediglich die Notation für den Zugriff auf Objekte von Linked Server einhalten. Der Zugriff erfolgt wie eine Wegbeschreibung und setzt sich aus vier Elementen zusammen:
[Server\Instanzname].[Datenbank].[Schema].[Table_Or_View]
Haben Sie zum Beispiel einen Linked Server mit dem Namen [SRV_FINANZ] und benötigen Informationen aus der Gehaltstabelle, die sich in der Datenbank [BUCHHALTUNG] befindet, wäre der Aufruf wie folgt durchzuführen:
SELECT * FROM [SRV_FINANZ].[BUCHHALTUNG].[dbo].[Gehaltstabelle]
Grundsätzlich funktioniert dieser Aufruf nur mit Tabellen / Views. Andere Objekte sind über diese Notationen nicht verfügbar.
Es ist aber nicht auszuschließen, dass z. B. eine UDF (User definied Function) aufgerufen werden muss, da die Entwickler direkten Zugriff auf Tabellen / Views untersagt haben. Dann kann obige Aufrufsyntax leider nicht verwendet werden.
Haben Sie zum Beispiel eine Funktion mit dem Namen fn_GehaltsListe(), die Ihnen eine Liste der aktuellen Gehaltsdaten zurückliefert, würden Sie bei dem Aufruf nach obigem Schema eine Fehlermeldung erhalten.
SELECT [SRV_FINANZ].[BUCHHALTUNG].[dbo].fn_GehaltsListe()
----------------------
Msg 207, Level 16, State 1, Line 1
Invalid Column name '[SRV_FINANZ]'
Der Aufruf wird also von der SQL Engine falsch interpretiert!
Abhilfe schafft jedoch OPENQUERY. Mit OPENQUERY werden Pass Through Abfragen auf Linked Server ausgeführt. Es gilt also immer die Syntax und die Konventionen des Zielservers! OPENQUERY hat jedoch einen Nachteil; sie dürfen keine Variablen als Parameter verwenden.
Für den Zugriff auf die Funktion wird OPENQUERY wie folgt verwendet:
SELECT * FROM OPENQUERY([SRV_FINANZ], 'SELECT [BUCHHALTUNG].[dbo].fn_GehaltsListe()')
Nun wird die Funktion erwartungsgemäß ausgeführt und liefert ein Ergebnis an den Client zurück!
Weiterführende Informationen:
64390 Erzhausen, 20.08.2010
Uwe Ricken
db Berater GmbH
Tücken bei der Verwendung eines Cursors in einem "clustered index vom Typen (n)varchar"
Jan 8th
Obwohl der Autor ein Gegner von Cursor ist, gibt es Situationen, in denen es einfach nicht möglich ist, eine andere Variante der Datenmanipulation zu favorisieren.
Das Cursor nicht gerade performant sind im Verhältnis zur mengenorientierten Datenmanipulation, sollte jedem erfahrenen SQL-Server Entwickler klar sein. Leider haben Cursor noch andere "Schwachstellen" an die der Autor unfreiwillig geraten ist.
Full story »