SQL Server - Rückgabe von SCHEMA für sysobjects

Question

Wie erhalte ich das SCHEMA, wenn ich ein Select auf sysobjects mache?

Ich modifiziere eine gespeicherte Prozedur namens SearchObjectsForText die nur den Namen zurückgibt, aber ich möchte auch das SCHEMA einschließen.

Im Moment macht es etwas Ähnliches wie das hier:

SELECT DISTINCT name
FROM sysobjects

Ich würde gerne wissen, welche Tabellen verbunden werden müssen, um das SCHEMA für jeden "Namen" zurückzugeben.

Answer 1

In SQL 200:

select DISTINCT
  name            as  ObjectName,     
  USER_NAME(uid)  as  SchemaName
from 
  sysobjects

In früheren Versionen von SQL Server konnten Datenbanken eine Entität namens "Schema" enthalten, aber diese Entität war eigentlich ein Datenbankbenutzer.

Answer 2

Es wurde eine Option hinzugefügt, um alle Objekte zu löschen, die mit einem bestimmten Präfix beginnen und optional aus einem bestimmten Schema stammen. Übrigens habe ich eine zusätzliche Abfrage hinzugefügt, um alle Typen zu erhalten, die nicht standardmäßig auf sysobjects gespeichert sind.

Ich habe das gesamte Beispielskript auf GitHub hochgeladen: DropAll_Dnn_Objects.sql

Teil 1: Temporäre Stored Procedure:

IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
    DROP PROCEDURE _temp_DropAllDnnObjects;
GO

CREATE PROCEDURE _temp_DropAllDnnObjects
    @object_prefix NVARCHAR(30),
    @schema_name sysname = NULL
AS
BEGIN
    DECLARE @sname sysname, @name sysname, @type NVARCHAR(30)
    DECLARE @object_type NVARCHAR(255), @sql NVARCHAR(2000), @count INT = 0

    DECLARE curs CURSOR FOR
        SELECT sname, [name], xtype 
        FROM (
            SELECT SCHEMA_NAME(schema_id) as sname, [name], [type] as xtype
                FROM sys.objects
                WHERE [type] IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')
                    AND name LIKE @object_prefix + '%'
                    AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
            UNION ALL
            SELECT SCHEMA_NAME(schema_id) as sname, [name], 'TYPE' as xtype
                FROM sys.types
                WHERE is_user_defined = 1
                    AND [name] LIKE @object_prefix + '%'
                    AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
            ) a
        ORDER BY CASE xtype
                        WHEN 'P'    THEN 1
                        WHEN 'FN'   THEN 2
                        WHEN 'IF'   THEN 3
                        WHEN 'TF'   THEN 4
                        WHEN 'TR'   THEN 5
                        WHEN 'V'    THEN 6
                        WHEN 'U'    THEN 7
                        WHEN 'TYPE' THEN 8
                        ELSE 9
                    END, name

    OPEN curs;
    FETCH NEXT FROM curs INTO @sname, @name, @type;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @count = @count + 1
        -- Configuration point 2
        SET @object_type = CASE @type
                        WHEN 'P'    THEN 'PROCEDURE'
                        WHEN 'FN'   THEN 'FUNCTION'
                        WHEN 'IF'   THEN 'FUNCTION'
                        WHEN 'TF'   THEN 'FUNCTION'
                        WHEN 'TR'   THEN 'TRIGGER'
                        WHEN 'V'    THEN 'VIEW'
                        WHEN 'U'    THEN 'TABLE'
                        WHEN 'TYPE' THEN 'TYPE'
                    END
        SET @sql = REPLACE(REPLACE(REPLACE('DROP <TYPE> [<SCHEMA>].[<NAME>];', 
                        '<TYPE>', @object_type),
                        '<SCHEMA>', @sname),
                        '<NAME>', @name)

        BEGIN TRY  
            PRINT @sql
            EXEC(@sql)
        END TRY  
        BEGIN CATCH  
            PRINT 'ERROR: ' + ERROR_MESSAGE()
        END CATCH  
        FETCH NEXT FROM curs INTO @sname, @name, @type;
    END;

    PRINT CONCAT('Objects Found: ', @Count)
    PRINT ''
    PRINT '------------------------------------------------------'
    PRINT ''

    CLOSE curs;
    DEALLOCATE curs;

    RETURN @Count
END;
GO

Bei Fehlern wird sie fortgesetzt (und die Fehlermeldung angezeigt). Es gibt eine Zählung aller gefundenen Objekte zurück.

Teil 2: Stored Procedure mit Parametern aufrufen:

Sie können eine WHILE-Schleife erstellen, um den Befehl so lange auszuführen, bis kein Objekt mehr vorhanden ist (Abhängigkeiten), wie folgt:

DECLARE @count INT = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'dnn';
SET @count = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'aspnet';
SET @count = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'vw_aspnet';
GO

Teil 3: Beenden Sie schließlich das Verfahren:

IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
    DROP PROCEDURE _temp_DropAllDnnObjects;
GO

Answer 3

Warum nicht statt einer Ansicht eine temporäre Tabelle auffüllen, die Sie verwenden können?

Diese Lösung verwende ich in gespeicherten Prozeduren

Dies ist der beste Weg, um ein Schema dynamisch zu erhalten und es zu den verschiedenen Tabellen innerhalb einer Datenbank hinzuzufügen, um andere Informationen dynamisch zu erhalten

select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

natürlich ist #tables eine dynamische Tabelle in der gespeicherten Prozedur