Jak zidentyfikować wszystkie procedury składowane odwołujące się do określonej tabeli

Stworzyłem tabelę na temat środowiska programistycznego do celów testowych i jest kilka programów, które odświeżają tę tabelę. Teraz muszę usunąć tę tabelę, a także zidentyfikować wszystkie sp, które odwołują się do tej tabeli. Mam trudności ze znalezieniem listy wszystkich sp. Proszę zasugerować zapytanie, zakładając, że nazwa tabeli to „x”, a baza danych to sql server 2005.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW - tutaj jest przydatny zasób dla tego typu pytań: Zapytanie do SQL Server System Catalog FAQ

Poniższe informacje działają na SQL2008 i nowszych wersjach. Zawiera listę zarówno procedur składowanych, jak i funkcji.

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
                    and sp.type in ('P', 'FN')
  where o.name = 'YourTableName'
  order by sp.Name

czasami powyższe zapytania nie dadzą poprawnego wyniku, dostępna jest wbudowana procedura składowana do pobrania zależności tabeli, jak:

EXEC sp_depends @objname = N'TableName';

Sposobem innym niż zapytania byłoby użycie Sql Server Management Studio.

Zlokalizuj tabelę, kliknij prawym przyciskiem myszy i wybierz „Wyświetl zależności”.

EDYTOWAĆ

Ale, jak powiedzieli komentatorzy, nie jest to zbyt wiarygodne.

Następujące zapytanie pobierze wszystkie nazwy procedur składowanych i odpowiadające im definicje tych SP

select 
   so.name, 
   text 
from 
   sysobjects so, 
   syscomments sc 
where 
   so.id = sc.id 
   and UPPER(text) like '%<TABLE NAME>%'

SELECT
    o.name
FROM
    sys.sql_modules sm
INNER JOIN sys.objects o ON
    o.object_id = sm.object_id
WHERE
    sm.definition LIKE '%<table name>%'

Należy tylko pamiętać, że spowoduje to również wyświetlenie SP tam, gdzie nazwa tabeli znajduje się w komentarzach lub gdy nazwa tabeli jest podłańcuchem innej używanej nazwy tabeli. Na przykład, jeśli masz tabele o nazwach „test” i „test_2” i spróbujesz wyszukać SP za pomocą „test”, otrzymasz wyniki dla obu.

Poniższe zapytanie działa tylko podczas wyszukiwania zależności w tabeli, a nie w kolumnie:

EXEC sp_depends @objname = N'TableName';

Jednak poniższe zapytanie jest najlepszą opcją, jeśli chcesz szukać wszelkiego rodzaju zależności, nie pomija niczego. W rzeczywistości dostarcza więcej informacji niż jest to wymagane.

 select distinct
        so.name
        --, text 
  from 
       sysobjects so, 
       syscomments sc 
  where 
     so.id = sc.id 
     and lower(text) like '%organizationtypeid%'
  order by so.name

W studiu zarządzania możesz po prostu kliknąć prawym przyciskiem myszy tabelę i kliknąć `` Wyświetl zależności ''

niż możesz zobaczyć listę obiektów, które mają zależności z twoją tabelą:

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'table_name' + '%'

GO

To zadziała, jeśli będziesz musiał podać nazwę tabeli.

Masz zasadniczo 2 opcje:

----Opcja 1

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Opcja 2

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Te 2 zapytania pozwolą uzyskać wszystkie procedury składowane, które odnoszą się do żądanej tabeli. To zapytanie opiera się na 2 tabelach sys, które są obiektami sysobject i syscomments. W sysobjects są przechowywane wszystkie nazwy obiektów DB, w tym procedury składowane.

Komentarze syscomments zawierają tekst wszystkich twoich procedur.

Jeśli zapytasz : SELECT * FROM syscomments

Będziesz mieć tabelę zawierającą identyfikator, który jest mapowaniem do tabeli sysobjects z tekstem zawartym w procedurach składowanych jako ostatnią kolumnę.

Spróbuj tego

   SELECT DISTINCT so.name
    FROM syscomments sc
    INNER JOIN sysobjects so ON sc.id=so.id
    WHERE sc.TEXT LIKE '%your table name%'