1. [代码]查询所有存储过程
01 |
select Pr_Name as [存储过程], [参数]=stuff((select '，'+[Parameter] |
02 |
from ( |
03 |
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter |
04 |
from sys.procedures Pr left join |
05 |
sys.parameters parameter on Pr.object_id = parameter.object_id |
06 |
inner join sys.types Type on parameter.system_type_id = Type.system_type_id |
07 |
where type = 'P' |
08 |
) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '') |
09 |
from ( |
10 |
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter |
11 |
from sys.procedures Pr left join |
12 |
sys.parameters parameter on Pr.object_id = parameter.object_id |
13 |
inner join sys.types Type on parameter.system_type_id = Type.system_type_id |
14 |
where type = 'P' |
15 |
)tb |
16 |
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%' |
17 |
group by Pr_Name |
18 |
order by Pr_Name |
2. [代码]查询所有触发器
01 |
select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用], |
02 |
triggers.is_instead_of_trigger AS [触发器类型], |
03 |
case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF' |
04 |
when triggers.is_instead_of_trigger = 0 then 'AFTER' |
05 |
else null |
06 |
end as [触发器类型描述] |
07 |
from sys.triggers triggers |
08 |
inner join sys.tables tables on triggers.parent_id = tables.object_id |
09 |
where triggers.type ='TR' |
10 |
order by triggers.create_date |
3. [代码]查询所有索引
01 |
select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as[索引列], |
02 |
Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键], |
03 |
Ind_Attribute.is_disabled AS [是否禁用] |
04 |
from ( |
05 |
select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] from |
06 |
( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name fromsys.indexes ind |
07 |
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2) |
08 |
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_idand ind.index_id = index_columns.index_id |
09 |
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id |
10 |
) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name for xml path('')), 1, 1,'') |
11 |
from ( |
12 |
select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind |
13 |
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2) |
14 |
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_idand ind.index_id = index_columns.index_id |
15 |
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id |
16 |
)tb |
17 |
where Tab_Name not like 'sys%' |
18 |
group by Tab_Name,Index_Name |
19 |
) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name |
20 |
order by indexs.Tab_Name |
4. [代码][SQL]代码
01 |
DECLARE @s VARCHAR(4000),@n INT,@i INT,@s1 VARCHAR(100) |
02 |
SELECT IDENTITY(INT) id,text INTO ## |
03 |
FROM syscomments |
04 |
SELECT @n=@@ROWCOUNT,@i=0 |
05 |
WHILE @i<@n |
06 |
BEGIN |
07 |
SELECT @i=@i+1,@s='' |
08 |
SELECT@s1=REPLACE(REPLACE(RTRIM(LTRIM(STUFF(STUFF(text,CHARINDEX('AS',text),40000,''),1, |
09 |
CHARINDEX('PROC',STUFF(text,CHARINDEX('AS',text),40000,''))+4,''))),CHAR(10),''),CHAR(13),'') |
10 |
FROM ## WHERE ID=RTRIM(@i) |
11 |
--SELECT @s1,ASCII(SUBSTRING(@s1,3,1)) |
12 |
--SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),''),CHAR(10),'')) |
13 |
SELECT @s='SELECT text FROM tempdb.dbo.## WHERE ID=' + RTRIM(@i) |
14 |
EXEC('EXEC master..xp_cmdshell ''bcp "' + @s + ' " queryout "e:\ProcTXT\' + @s1 + '.txt" -S"ROBINHOME\SQLEXPRESS" -c -U"sa" -P"bd5178"''') |
15 |
END |
16 |
DROP TABLE ## |
17 |
18 |
--自己写的 如何识别换行？？？ 还有些非存储过程的对象 |
19 |
SELECT top 10 text FROM syscomments where id in( |
20 |
select object_id from sys.procedures where type = 'P') |
21 |
22 |
SELECT text FROM syscomments where id in( |
23 |
select object_id from sys.procedures where type = 'P') |
24 |
and charindex('ALLSTOCK',text)>0 |
25 |
and charindex('CREATE PROCEDURE',text)>0 |
