SQL Server查询所有存储过程信息、触发器、索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server查询所有存储过程信息、触发器、索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4857字，纯文字阅读大概需要7分钟。
内容图文
1. [代码]查询所有存储过程 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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. [代码]查询所有触发器 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 4 5 6 7 8 9 10 |
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
|
3. [代码]查询所有索引 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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
|
4. [代码][SQL]代码 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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)
--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 "e:\ProcTXT\‘ + @s1 + ‘.txt" -S"ROBINHOME\SQLEXPRESS" -c -U"sa" -P"bd5178"‘‘‘)
END
DROP TABLE ##
--自己写的 如何识别换行？？？ 还有些非存储过程的对象
SELECT top 10 text FROM syscomments where id in(
select object_id from sys.procedures where type = ‘P‘)
SELECT text FROM syscomments where id in(
select object_id from sys.procedures where type = ‘P‘)
and charindex(‘ALLSTOCK‘,text)>0
and charindex(‘CREATE PROCEDURE‘,text)>0
|
5. [代码]显示存储过程内容 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 |
SELECT TEXT FROM syscomments WHERE id=object_id(‘SP_NAME‘)
SP_HELPTEXT ‘SP_NAME‘
|
6. [代码]获取只有用户定义的存储过程 跳至 [1] [2] [3] [4] [5] [6] [全屏预览]
?| 1 2 3 4 5 6 |
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‘
|
SQL Server查询所有存储过程信息、触发器、索引
标签：
本文系统来源：http://www.cnblogs.com/lvdongjie/p/5555204.html
内容总结
以上是互联网集市为您收集整理的SQL Server查询所有存储过程信息、触发器、索引全部内容，希望文章能够帮你解决SQL Server查询所有存储过程信息、触发器、索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】