SqlServer查询数据库所有存储过程、触发器、索引信息

分手后的思念是犯贱 2021-06-26 16:06 652阅读 0赞

## 1. 查询所有存储过程 ##

SELECT pr_name AS [存储过程],
           [参数]=Stuff((SELECT '，' + [parameter]
                           FROM   (SELECT Pr.NAME AS Pr_Name,
                                          parameter.NAME + ' ' + Type.NAME +
    ' ('
    + CONVERT(VARCHAR(32), parameter.max_length)
    + ')'   AS Parameter
    FROM   sys.procedures Pr
    LEFT JOIN sys.parameters parameter
           ON Pr.object_id =
              parameter.object_id
    INNER JOIN sys.types Type
            ON parameter.system_type_id =
               Type.system_type_id
    WHERE  type = 'P') t
    WHERE  pr_name = tb.pr_name
    FOR xml path('')), 1, 1, '')
    FROM   (SELECT Pr.NAME AS Pr_Name,
    parameter.NAME + ' ' + Type.NAME + ' ('
    + CONVERT(VARCHAR(32), parameter.max_length)
    + ')'   AS Parameter
    FROM   sys.procedures Pr
    LEFT JOIN sys.parameters parameter
    ON Pr.object_id = parameter.object_id
    INNER JOIN sys.types Type
    ON parameter.system_type_id = Type.system_type_id
    WHERE  type = 'P')tb
    WHERE  pr_name NOT LIKE 'sp_%' --and Pr_Name not like 'dt%'
    GROUP  BY pr_name
    ORDER  BY pr_name

## 2. 存储过程信息查询 ##

SELECT Pr.NAME                                    AS Pr_Name,
           parameter.NAME,
           T.NAME,
           CONVERT(VARCHAR(32), parameter.max_length) AS 参数长度,
           parameter.is_output                        AS 是否是输出参数,
           parameter.*
    FROM   sys.procedures Pr
           LEFT JOIN sys.parameters parameter
                  ON Pr.object_id = parameter.object_id
           INNER JOIN sys.types T
                   ON parameter.system_type_id = T.system_type_id
    WHERE  Pr.type = 'P'
           AND Pr.NAME LIKE 'order_%'
           AND T.NAME != 'sysname'
    ORDER  BY Pr.NAME

## 3. 显示存储过程内容 ##

SELECT text
    FROM   syscomments
    WHERE  id=Object_id('SP_NAME')

EXEC SP_HELPTEXT 'SP_NAME'

## 4. 查询所有触发器 ##

SELECT triggers.NAME                  AS [触发器],
           tables.NAME                    AS [表名],
           triggers.is_disabled           AS [是否禁用],
           triggers.is_instead_of_trigger AS [触发器类型],
           CASE
             WHEN triggers.is_instead_of_trigger = 1 THEN 'INSTEAD OF'
             WHEN triggers.is_instead_of_trigger = 0 THEN 'AFTER'
             ELSE NULL
           END                            AS [触发器类型描述]
    FROM   sys.triggers triggers
           INNER JOIN sys.tables tables
                   ON triggers.parent_id = tables.object_id
    WHERE  triggers.type = 'TR'
    ORDER  BY triggers.create_date

## 5. 查询所有索引 ##

SELECT indexs.tab_name              AS [表名],
           indexs.index_name            AS [索引名],
           indexs.[co_names]            AS [索引列],
           Ind_Attribute.is_primary_key AS [是否主键],
           Ind_Attribute.is_unique      AS [是否唯一键],
           Ind_Attribute.is_disabled    AS [是否禁用]
    FROM   (SELECT tab_name,
                   index_name,
                   [Co_Names]=Stuff((SELECT '，' + [co_name]
                                     FROM   (SELECT tab.NAME AS Tab_Name,
                                                    ind.NAME AS Index_Name,
                                                    Col.NAME AS Co_Name
                                             FROM   sys.indexes ind
                                                    INNER JOIN sys.tables tab
                                                            ON ind.object_id =
                                                               tab.object_id
                                                               AND ind.type IN ( 1,
                                                                   2 )
                                                    INNER JOIN sys.index_columns
                                                               index_columns
                                                            ON tab.object_id =
    index_columns.object_id
    AND ind.index_id =
        index_columns.index_id
    INNER JOIN sys.columns Col
    ON tab.object_id =
    Col.object_id
    AND index_columns.column_id
        =
        Col.column_id) t
    WHERE  tab_name = tb.tab_name
    AND index_name = tb.index_name
    FOR xml path('')), 1, 1, '')
    FROM   (SELECT tab.NAME AS Tab_Name,
    ind.NAME AS Index_Name,
    Col.NAME AS Co_Name
    FROM   sys.indexes ind
    INNER JOIN sys.tables tab
    ON ind.object_id = tab.object_id
    AND ind.type IN ( 1, 2 )
    INNER JOIN sys.index_columns index_columns
    ON tab.object_id = index_columns.object_id
    AND ind.index_id = index_columns.index_id
    INNER JOIN sys.columns Col
    ON tab.object_id = Col.object_id
    AND index_columns.column_id = Col.column_id)tb
    WHERE  tab_name NOT LIKE 'sys%'
    GROUP  BY tab_name,
    index_name) indexs
    INNER JOIN sys.indexes Ind_Attribute
    ON indexs.index_name = Ind_Attribute.NAME
    ORDER  BY indexs.tab_name