查询所有存储过程
2010-11-26 22:33:35| 分类：
默认分类
| 标签：
|举报
|字号大中小 订阅
--查询所有存储过程
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)/*索引的类型：0=堆/1=聚集/2=非聚集/3=XML*/
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)/*索引的类型：0=堆/1=聚集/2=非聚集/3=XML*/
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)
--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
在一个应用系统中，如果编写了大量的Sybase存储过程，对存储过程进行系统、有效地备份是必须的。而我们通常用的办法是使用Sybase提供的 Sybase Central工具先选定存储过程，然后通过鼠标右键选择Generate DDL的方式把存储过程备份下来。使用这种方式，不仅备份时间特别长（备份文件为6M左右时，达几个小时之久），更要命的是使用该方式备份下来的存储过程文本往往无法直接使用，因为在每个存储过程体后自动加的关键字go，有时就直接放在过程体最后一行尾部，没有空格，这样要使用该文本重新建过程时，需要花大量的时间进行修正。因此，找一种备份速度既快备份后内容又规整的方法来替代该工具是完全有必要的。
笔者在实际工作中，交*使用以下两种方法，对存储过程进行备份。方法一：通过编写嵌入sql的C程序，实现整库存储过程的快速导出。方法二：通过defncopy快速导出指定的存储过程。现分别详细介绍如下：
[b]方法一：通过cpre实现快速导出[/b]
以下程序通过sybase的cpre预编译处理生成可执行文件后，可以实现存储过程的快速导出，在数据库空闲时，可以在一分钟之内导出所有的存储过程（生成的文件有6M左右，而Sybase Central需要以小时计）。
该程序运行时格式如下（设可执行文件为exportproc）：
sybase> exprotproc 文件名
执行完后，所有的存储过程体就存放在该文件中，文件的内容也保存了存储过程的书写格式，并在每个存储过程后面加一个单独的行go。为了使程序灵活，在正式导之前根据提示需要输入用户名、口令、联机串、数据库名。
#include
#include
EXEC SQL INCLUDE SQLCA;
void Sql_Error();
main( argc, argv)
int argc;
char **argv;
{
FILE *fp;
char useName[11], usePasswd[11], dbString[16];
char *fpass;
long oldId;
EXEC SQL BEGIN DECLARE SECTION;
char textLine[256];
long id;
char dbName[20];
EXEC SQL END DECLARE SECTION;
/*输入参数不够，给出使用提示*/
if (argc!=2){
printf("Usage As %s \n", argv[0]);
exit(0);
}
if( (fp=fopen(argv[1],"w+"))==NULL){
printf("Open file %s error\n",argv[1]);
exit(0);
}
/*输入联库用户名*/
printf("Please Input user name:");
gets(useName);
/*输入用户口令，在输入时屏幕不显示输入内容*/
fpass=getpass("Please Input Passwd:");
strcpy(usePasswd,fpass);
/*输入联库字符串*/
printf("Please Input Database string:");
gets(dbString);
/*输入要导出其中存储过程的库名*/
printf("Please Input Database Name:");
gets(dbName);
EXEC SQL WHENEVER SQLERROR CALL Sql_Error();
if( ConnectDB(useName, usePasswd, dbString) !=0){ /*连接数据库*/
printf("Can't connect database\n");
exit(0);
}
else
printf("Connect database ok!\n");
printf("Begin to export PROCEDURE Please wait...\n");
EXEC SQL use :dbName;
EXEC SQL commit;
/*声明游标,找出该库中所有的存储过程对应的内容*/
EXEC SQL declare pro_cur cursor for
SELECT id, text from syscomments
where id in (select id from sysobjects
where type = 'P')
and texttype=0
and text is not null
order by id, number,colid2,colid;
EXEC SQL OPEN pro_cur;
EXEC SQL FETCH pro_cur into :id, :textLine;
oldId=-9999L;
while(!sqlca.sqlcode){
/*当一个存储过程结束后，在其过程体后加入新行GO*/
fprintf(fp,"\nGO\n\n");
}
oldId=id;
fprintf(fp,"%s",textLine);
EXEC SQL FETCH pro_cur INTO :id, :textLine;
}
EXEC SQL CLOSE pro_cur;
fprintf(fp,"\nGO\n\n");
fclose(fp);
printf("End export PROCEDURE !\n");
/*断开于数据库的连接*/
DisConnectDB();
printf("Disconnect DataBase success!\n");
}
void Sql_Error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("Error.\n");
printf("%s\n",sqlca.sqlerrm.sqlerrmc);
exit(0);
}
/*连接数据库函数*/
int ConnectDB( username, password, dbstring)
EXEC SQL BEGIN DECLARE SECTION;
CS_CHAR *username,*password,*dbstring;
EXEC SQL END DECLARE SECTION;
{
EXEC SQL SET CHAINED OFF;
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :dbstring;
return(sqlca.sqlcode);
}
/*断开数据库函数*/
int DisConnectDB()
{
EXEC SQL DISCONNECT ALL;
return 0;
}
该程序在IBM AIX4.3、hp－unix 11.0、tru64 unix5.0平台上测试通过。
[b]方法二：通过defncopy实现快速导出[/b]
在需要对一些存储过程单独进行备份时，往往使用defncopy通过拼串的方式进行。具体步骤如下：
1.根据需要备份的存储过程，先编写此crtprocout.sql文件，假设导出所有以PR_JF开头的存储过程，内容如下：
select "defncopy -U用户名 -P口令 -S联机串名 out "+name+".sql 库名 "+name from sysobjects where type='P' and name like "PR_JF%"
2.利用上述文件，生成导过程脚本
isql -U用户名 -P口令 -S联机串名 –I crtprocout.sql –o procout
3.改变文件procout的权限
chmod +x procout
4.执行脚本导出过程，每个过程脚本名为：过程名+后缀”.sql”
./procout
小结：以上两种办法通过修改sql语句里的where条件相互之间是可以代替的。但笔者认为，前者适合对整库的过程进行备份，而后者适合对指定的几个过程进行备份。因为前者会对所有的过程脚本生成到一个文件里，适合面向多个过程的管理和备份；而后者一个过程脚本生成一个文件，适合面向单个过程的管理和备份。以上两种方法通过简单修改也可进行触发器等的导出。
评论这张
转发至微博
转发至微博
关闭
玩LOFTER，免费冲印20张照片，人人有奖！
我要抢>
<#--最新日志，群博日志-->
<#--推荐日志-->
<#--引用记录-->
<#--博主推荐-->
<#--随机阅读-->
<#--首页推荐-->
<#--历史上的今天-->
<#--被推荐日志-->
<#--上一篇，下一篇-->
<#-- 热度 -->
<#-- 网易新闻广告 -->
<#--右边模块结构-->
<#--评论模块结构-->
<#--引用模块结构-->
<#--博主发起的投票-->
评论