Jak sprawdzić, czy funkcja istnieje w bazie danych SQL

138

Muszę dowiedzieć się, czy funkcja istnieje w bazie danych, aby móc ją porzucić i ponownie utworzyć. Zasadniczo powinno to być coś podobnego do następującego kodu, którego używam dla procedur składowanych:

IF EXISTS (
     SELECT  *
     FROM    dbo.sysobjects
     WHERE   id = OBJECT_ID(N'[dbo].[SP_TEST]')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )

sql sql-server sql-function

— Dr Greenthumb
źródło

206

To jest to, czego używa SSMS podczas wykonywania skryptów przy użyciu DROP and CREATEopcji

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[foo]

GO

Takie podejście do wdrażania zmian oznacza, że musisz ponownie utworzyć wszystkie uprawnienia do obiektu, aby ALTERzamiast tego rozważyć opcję- rozważenie, czy istnieje.

— Martin Smith
źródło

17

Jeszcze bardziej zastanawiam się, dlaczego nie ma widoku katalogu systemu sys.functions .....

— marc_s

61

Zwykle używam Information_Schema:

IF EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Foo'
                    AND Routine_Type = 'FUNCTION' )

dla funkcji i zmiany Routine_Typedla procedur składowanych

IF EXISTS ( SELECT  1
            FROM    Information_schema.Routines
            WHERE   Specific_schema = 'dbo'
                    AND specific_name = 'Foo'
                    AND Routine_Type = 'PROCEDURE' )

— Prawo Metzlera
źródło

2

Fajnie szukałem czegoś takiego i nigdy tego nie znalazłem. Uważam, że ogólnie lepiej jest używać schematu information_schema, ponieważ nie jest on powiązany z określonym systemem RDBMS. (przy okazji pojęcie kompatybilności między platformami pochodzi z tej odpowiedzi: stackoverflow.com/a/14290099/420667 )

— user420667

40

Dlaczego nie tylko:

IF object_id('YourFunctionName', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[YourFunctionName]
END
GO

Drugi argument funkcji object_idjest opcjonalny, ale może pomóc w zidentyfikowaniu właściwego obiektu. Istnieje wiele możliwych wartości dla tego argumentu typu, w szczególności:

FN: funkcja skalarna
IF: wbudowana funkcja wyceniana w tabeli
TF: funkcja z wartościami tabelarycznymi
FS: Funkcja skalarna zestawu (CLR)
FT: Funkcja wartości tabelarycznej zestawu (CLR)

— Kapé
źródło

4

Technicznie może się to nie powieść, ponieważ sprawdza tylko, czy istnieje obiekt o tej nazwie. Nie chodzi o to, że istnieje obiekt i jest to funkcja. EG Jeśli CREATE TABLE YourFunctionName(X INT);następnie uruchomienie kodu zakończy się niepowodzeniem.

— Martin Smith

1

@MartinSmith: Łatwe do wykonania solidne. Po prostu użyj object_id('YourFunction', 'FN')lub dowolnego innego oznaczenia (drugiego argumentu), który jasno określi, do jakiego rodzaju obiektu się odnosisz.

— kochanie,

@darlove używający „FN” jako drugiego parametru może nie działać. Właśnie się dowiedziałam. „FN” oznacza funkcję skalarną. To łącze informuje o różnych wartościach parametrów, które można przekazać sqlhints.com/tag/how-to-check-if-function-exists . Ciągle używam `` FN '' do sprawdzania istniejącej funkcji wartości tabeli i nie działa. Muszę użyć „TF”

— user12345

9

Odkryłem, że możesz użyć bardzo nierozpoznawalnego i prostego podejścia do sprawdzania istnienia różnych obiektów SQL Server w ten sposób:

IF OBJECTPROPERTY (object_id('schemaname.scalarfuncname'), 'IsScalarFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.tablefuncname'), 'IsTableFunction') = 1
IF OBJECTPROPERTY (object_id('schemaname.procname'), 'IsProcedure') = 1

Jest to oparte na funkcji OBJECTPROPERTY, która jest dostępna w SQL 2005+. Artykuł MSDN można znaleźć tutaj .

Funkcja OBJECTPROPERTY używa następującego podpisu:

OBJECTPROPERTY ( id , property )

Przekazujesz wartość literału do parametru właściwości, wyznaczając typ obiektu, którego szukasz. Istnieje ogromna lista wartości, które możesz podać.

— Jeremy
źródło

Myślę, że łatwiej byłoby zobaczyć prostotę tej odpowiedzi, gdyby zawierała kompletny przykład if / drop.

— Jonathan

6

Wiem, że ten wątek jest stary, ale chciałem tylko dodać tę odpowiedź dla tych, którzy uważają, że jest to bezpieczniejsze Alterniż Dropi Create. Poniżej Alterprzedstawiamy, Functionczy istnieje Create, czy nie:

  IF NOT EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[foo]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
       EXEC('CREATE FUNCTION [dbo].[foo]() RETURNS INT AS BEGIN RETURN 0 END')
  GO
  ALTER FUNCTION [dbo].[foo]
  AS
  ...

— jamiedanq
źródło

2

Podoba mi się to, ale myślę, że powinno być „ZMIENIONA FUNKCJA”, nie?

— Erik

LubięALTER OR CREATE

— AgentFire