SQL Server查询所有存储过程信息、触发器、索引

SQL Server查询所有存储过程信息、触发器、索引

查询所有存储过程

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

触发器

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;

索引

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;

代码

DECLARE @s VARCHAR(4000) ,
    @n INT ,
    @i INT ,
    @s1 VARCHAR(100);
SELECT  IDENTITY( INT ) id ,
        text
INTO    ##
FROM    syscomments;
SELECT  @n = @@ROWCOUNT ,
        @i = 0;
WHILE @i < @n
    BEGIN
        SELECT  @i = @i + 1 ,
                @s = '';
        SELECT  @s1 = REPLACE(REPLACE(RTRIM(LTRIM(STUFF(STUFF(text,
                                                              CHARINDEX('AS',
                                                              text), 40000, ''),
                                                        1,
                                                        CHARINDEX('PROC',
                                                              STUFF(text,
                                                              CHARINDEX('AS',
                                                              text), 40000, ''))
                                                        + 4, ''))), CHAR(10),
                                      ''), CHAR(13), '')
        FROM    ##
        WHERE   id = RTRIM(@i);
--end
--SELECT * FROM ##
--SELECT @s1,ASCII(SUBSTRING(@s1,3,1))
--SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),''),CHAR(10),''))
        SELECT  @s = 'SELECT text FROM tempdb.dbo.## WHERE ID=' + RTRIM(@i);
        EXEC('EXEC master..xp_cmdshell ''bcp "' + @s + ' " queryout "d:hou' + @s1 + '.txt" -S"ROBINHOMESQLEXPRESS" -c -U"sa" -P"bd5178"''');
    END;
DROP TABLE ##;

显示存储过程内容：

SELECT TEXT FROM syscomments WHERE id=object_id('SP_NAME')
 
SP_HELPTEXT 'SP_NAME'

获取只有用户定义的存储过程

USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC') AND [is_ms_shipped] = 0 ORDER BY [name];
GO
SELECT * FROM sysobjects where type='P'