Wie überprüfen Sie alle gespeicherten Verfahren ist ok in Sql-Server?

Question

Wie alle gespeicherte Prozedur zu überprüfen ist ok in Sql Server, wenn ich eine Tabelle oder Felder löschen?

Answer 1

Ein paar Möglichkeiten, die mir einfallen

1. Der naheliegendste Weg, die Verfahren auszuführen
2. Prüfen Sie die Abhängigkeiten der Tabelle, bevor Sie die Tabelle oder ein Feld löschen. Prüfen Sie dann die abhängigen Prozesse
3. Skripte für alle Verfahren erstellen und nach diesem Feld oder dieser Tabelle suchen
4. Abfrage von sysobjects

Answer 2

Gleiche Idee, aber universeller - Sie prüfen alle benutzerdefinierten Objekte mit Körpern Und es zeigt Ihnen Fehler beim Kompilieren an. Dies ist sehr nützlich nach dem Umbenennen/Entfernen von Objekten/Spalten usw.

Führen Sie es einfach nach der Aktualisierung des Datenbankschemas aus, um sicherzustellen, dass alle Body-Objekte noch gültig sind.

DECLARE @obj_name AS sysname, @obj_type AS sysname

DECLARE obj_cursor CURSOR FOR 
    SELECT SCHEMA_NAME(o.schema_id) + '.' + o.name, o.type_desc 
    FROM sys.objects o 
    INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
    WHERE o.is_ms_shipped = 0 AND m.is_schema_bound = 0 
    ORDER BY o.type_desc, SCHEMA_NAME(o.schema_id), o.name

OPEN obj_cursor 
FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
    BEGIN TRY
        EXEC sp_refreshsqlmodule @obj_name
        --PRINT 'Refreshing ''' + @obj_name + ''' completed'
    END TRY
    BEGIN CATCH
        PRINT 'ERROR - ' + @obj_type + ' ''' + @obj_name + ''':' + ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM obj_cursor INTO @obj_name, @obj_type
END 

CLOSE obj_cursor
DEALLOCATE obj_cursor

Answer 3

Keine der gegebenen Antworten kann den Fehler finden, der durch das Umbenennen oder Löschen einer Tabelle entsteht
aber seien Sie froh, ich habe eine Lösung für SQL Server 2017 und höhere Versionen:

DECLARE @NumberRecords INT
DECLARE @RowCount INT
DECLARE @Name NVARCHAR(MAX)
DECLARE @Command NVARCHAR(MAX)
DECLARE @Result int
DECLARE @Names TABLE (
    [RowId] INT NOT NULL    IDENTITY(1, 1),
    [Name]  NVARCHAR(MAX),
    [Type]  NVARCHAR(MAX)
)

INSERT INTO @Names
SELECT
        QUOTENAME(SCHEMA_NAME([Objects].schema_id)) + '.' + QUOTENAME(OBJECT_NAME([Objects].object_id)) [Name],
        type_desc [Type]
FROM sys.objects [Objects]
WHERE type_desc IN ('SQL_STORED_PROCEDURE',
                    'SQL_TRIGGER',
                    'SQL_SCALAR_FUNCTION',
                    'SQL_TABLE_VALUED_FUNCTION',
                    'SQL_INLINE_TABLE_VALUED_FUNCTION',
                    'VIEW')
ORDER BY [Name]

SET @RowCount = 1
SET @NumberRecords = (SELECT COUNT(*) FROM @Names)
WHILE (@RowCount <= @NumberRecords)
BEGIN

    SELECT @Name = [Name]
    FROM @Names
    WHERE [RowId] = @RowCount

    SET @Command = N'EXEC sp_refreshsqlmodule ''' + @Name + ''''

    BEGIN TRY

        EXEC @Result = sp_executesql @Command

        IF @Result <> 0
        BEGIN

            RAISERROR('Failed', 16, 1)

        END
        ELSE
        BEGIN

            IF (NOT EXISTS (SELECT *
                            FROM sys.dm_sql_referenced_entities(@Name, 'OBJECT')
                            WHERE [is_incomplete] = 1))
            BEGIN

                DELETE
                FROM @Names
                WHERE [RowId] = @RowCount

            END

        END

    END TRY
    BEGIN CATCH

        -- Nothing

    END CATCH

    SET @RowCount = @RowCount + 1

END

SELECT  [Name],
        [Type]
FROM @Names

Answer 4

Ich habe es mit der "Cade Roux"-Antwort versucht, es ging schief und ich habe es wie folgt korrigiert

 SELECT 'BEGIN TRAN T1;' UNION
    SELECT   REPLACE('BEGIN TRY
    EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
      END TRY
      BEGIN CATCH
    PRINT ''{OBJECT_NAME} IS INVALID.'' 
     END CATCH', '{OBJECT_NAME}',
                                              QUOTENAME(ROUTINE_SCHEMA) + '.'
                                              + QUOTENAME(ROUTINE_NAME))
    FROM    INFORMATION_SCHEMA.ROUTINES
    WHERE   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                     + QUOTENAME(ROUTINE_NAME)),
                           N'IsSchemaBound') IS NULL
            OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
                                        + QUOTENAME(ROUTINE_NAME)),
                              N'IsSchemaBound') = 0
                              UNION 
                            SELECT  'ROLLBACK TRAN T1;'

Answer 5

Mein Ansatz war ein wenig anders. Ich habe Alter Skript für eine Reihe von procs in SSMS erstellt und dann für einige Sekunden gewartet, so SSMS verarbeiten sie und Ive bekam, was ich wollte:

O dann SSMS rechten Rand einen roten Punkt für jede Zeile in Fehler, die ich leicht zu überprüfen, zu korrigieren und später das gleiche Skript ausführen, um mit richtigen Werten zu aktualisieren.