Comment puis-je liste tous les non-procédures stockées système?

Je veux créer une requête à la liste de tous définis par l'utilisateur des procédures stockées, à l'exclusion de ceux qui sont des procédures stockées du système, en considérant que:

  1. Vérifiant le nom comme "sp_" ne fonctionne pas parce qu'il y a de l'utilisateur des procédures stockées qui commencent par "sp_".
  2. La vérification de la propriété is_ms_shipped ne fonctionne pas parce qu'il y a des procédures stockées du système qui ont le drapeau = 0, par exemple: sp_alterdiagram (il n'est pas MSShipped mais apparaît sous Système de Procédures Stockées dans SQL Server Management Studio).

Il doit y avoir une propriété, ou un drapeau quelque part, puisque vous pouvez voir le "Système de Procédures Stockées" dans un dossier distinct dans SQL 2005. Personne ne sait?

Edit: Une combinaison des suggestions ci-dessous a fonctionné pour moi:

select *
from 
    sys.objects             O LEFT OUTER JOIN
    sys.extended_properties E ON O.object_id = E.major_id
WHERE
    O.name IS NOT NULL
    AND ISNULL(O.is_ms_shipped, 0) = 0
    AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
    AND O.type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY O.name
InformationsquelleAutor bubbassauro | 2009-12-29
  1. 14

    Vous devriez utiliser quelque chose comme ceci:

    select * from sys.procedures where is_ms_shipped = 0

    Comme vous avez pu le deviner, la clé est dans is_ms_shipped attribut (il existe dans sys.les objets de la vue ainsi).

    Mis à JOUR. D'abord manqué votre point sur is_ms_shipped.

    C'est le code (condition) que la Gestion de Studio utilise pour récupérer une liste de procédures stockées système'

     CAST(
 case 
 when sp.is_ms_shipped = 1 then 1
 when (
    select 
        major_id 
    from 
        sys.extended_properties 
    where 
        major_id = sp.object_id and 
        minor_id = 0 and 
        class = 1 and 
        name = N''microsoft_database_tools_support'') 
    is not null then 1
 else 0
 end AS BIT) = 1

    Ici ps fait référence à sys.all_objects vue du système.

    • il mentionne le is_ms_shipped dans sa question et dit qu'il n'est pas fiable.
    • Manqué au début, mais une mise à jour de ma suggestion un peu plus tard.
    InformationsquelleAutor AlexS
  2. 4

    à l'aide de la première réponse ci-dessus, j'ai écrit ce qui fonctionne bien pour mes utilisations:

    select
        *
    from
        INFORMATION_SCHEMA.ROUTINES as ISR
    where
        ISR.ROUTINE_TYPE = 'PROCEDURE' and
        ObjectProperty (Object_Id (ISR.ROUTINE_NAME), 'IsMSShipped') = 0 and
        (
            select 
                major_id 
            from 
                sys.extended_properties 
            where 
                major_id = object_id(ISR.ROUTINE_NAME) and 
                minor_id = 0 and 
                class = 1 and 
                name = N'microsoft_database_tools_support'
        ) is null
    order by
        ISR.ROUTINE_CATALOG,
        ISR.ROUTINE_SCHEMA,
        ISR.ROUTINE_NAME
    InformationsquelleAutor Jeffrey Bradley
  3. 4

    Je vais juste jeter dans ma version "améliorée" de SQL (sachant que le formatage est une question de préférence personnelle):

    SELECT *
FROM [sys].[procedures] sp
WHERE is_ms_shipped = 0
AND NOT EXISTS (
    select ep.[major_id]
    from [sys].[extended_properties] ep
    where ep.[major_id] = sp.[object_id]
    and ep.[minor_id] = 0
    and ep.[class] = 1
    and ep.[name] = N'microsoft_database_tools_support')
    InformationsquelleAutor Abacus
  4. 3

    Voici ce que j'ai fait de base sur les solutions ci-dessus:

    select * from sys.procedures 
 where object_id not in(select major_id from sys.extended_properties)

    Cette simple requête fonctionne sur SQL Server 2008 mais je n'ai pas testé les autres versions.

    Mise à JOUR - 2018-11-28

    Travaille également sur SQL Server 2014

    • Il travaille sur 2014 aussi..
    InformationsquelleAutor Christian Mark
  5. 2

    Il y a trois sortes de "système" procédures:

    • Vrai SQL procédures, celles de l' 'sys' schéma, sera constaté que les procédures ordinaires de mssqlsystemresource base de données.
    • Utilisateur ordinaire procédures installé par les différents composants. Ce sont les goûts de procédures de réplication, la collecte de données, le suivi des modifications, déclarative managmenet cadre et d'autres. Ils ne sont pas de système du tout, ils vivent dans le " dbo "schéma et sont tout simplement commercialisé en tant que "système". Certains peuvent être identifiés par le "IsMSShipped' drapeau, mais pas tous.
    • launguage pseudo-procédures. Ce sont des T-SQL desquised que les procédures et vous ne trouverez pas n'importe où.
    InformationsquelleAutor Remus Rusanu
  6. 1

    essayer cette

    select * from DatabaseName.information_schema.routines where routine_type = 'PROCEDURE'

    Si pour quelque raison vous avez eu de la non-système de procédures stockées dans la base de données, vous pouvez utiliser la requête (ce qui permettra de filtrer la PLUPART des procédures stockées du système:

    select * from master.information_schema.routines where routine_type = 'PROCEDURE' and
Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

    vous voir plus d'informations dans la réponse suivante

    Requête qui retourne la liste de toutes les Procédures Stockées

    InformationsquelleAutor Hiyasat