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

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