存储过程在数据库中的存储问题

[winsor_w]

现在在做一个比较两个数据库是否相同的小系统，主要就是比较 数据表、视图、存储过程、函数这四类是否相同。
现在问题是，要比较存储过程或函数的时候，不知道它们存在SQL server的什么地方，Oracle中是存在视图中，但SQL server就不知道它们存在哪里了，我说的是存储过程和函数的主题部分，也就是我们平时写的存储过程或函数的内容是存在什么地方（是否也在某个系统表中，但我怎么找都没找到）。 如果能告诉我怎么比较两个数据库中的存储过程或函数是否相同的方法，那就更好了。

[dawugui]

邹老大以前写的一个存储过程，用于比较比较两个数据库的表结构差异： 


SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO

/*--比较两个数据库的表结构差异
    
    可以比较两个数据库的结构差异

--邹建 2003.9(引用请保留此信息)--*/

/*--调用示例
    
    exec p_comparestructure '库1','库2'
--*/
create proc p_comparestructure
@dbname1 varchar(250),    --要比较的数据库名1
@dbname2 varchar(250)    --要比较的数据库名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
    占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)

create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
    占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 sql_variant,字段说明 sql_variant)

--得到数据库1的结构
exec('insert into #tb1 SELECT 
    表名=d.name,字段名=a.name,序号=a.colid,
    标识=case when a.status=0x80 then 1 else 0 end,
    主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and parent_obj=a.id and name in (
        SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
            SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
        ))) then 1 else 0 end,
    类型=b.name,    占用字节数=a.length,长度=a.prec,小数位数=a.scale,    允许空=a.isnullable,
    默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM '+@dbname1+'..syscolumns a
    left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
    inner join '+@dbname1+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
    left join '+@dbname1+'..syscomments e on a.cdefault=e.id
    left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid  
order by a.id,a.colorder')

--得到数据库2的结构
exec('insert into #tb2 SELECT 
    表名=d.name,字段名=a.name,序号=a.colid,
    标识=case when a.status=0x80 then 1 else 0 end,
    主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and parent_obj=a.id and name in (
        SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
            SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
        ))) then 1 else 0 end,
    类型=b.name,    占用字节数=a.length,长度=a.prec,小数位数=a.scale,    允许空=a.isnullable,
    默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM '+@dbname2+'..syscolumns a
    left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
    inner join '+@dbname2+'..sysobjects d on a.id=d.id  and d.xtype=''U'' and  d.name<>''dtproperties''
    left join '+@dbname2+'..syscomments e on a.cdefault=e.id
    left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid  
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
        when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
        when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
        when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
        when a.标识<>b.标识 then '标识不同'
        when a.主键<>b.主键 then '主键设置不同'
        when a.类型<>b.类型 then '字段类型不同'
        when a.占用字节数<>b.占用字节数 then '占用字节数'
        when a.长度<>b.长度 then '长度不同'
        when a.小数位数<>b.小数位数 then '小数位数不同'
        when a.允许空<>b.允许空 then '是否允许空不同'
        when a.默认值<>b.默认值 then '默认值不同'
        when a.字段说明<>b.字段说明 then '字段说明不同'
    else '' end,
    *
    from #tb1 a
    full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null 
    or a.标识<>b.标识 or a.主键<>b.主键 or a.类型<>b.类型
    or a.占用字节数<>b.占用字节数 or a.长度<>b.长度 or a.小数位数<>b.小数位数
    or a.允许空<>b.允许空 or a.默认值<>b.默认值 or a.字段说明<>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)

[-狙击手-]

嘿嘿

[changjiangzhibin]

眼冒金星了

[winsor_w]

数据库表跟视图我都弄出来了，主要是函数跟存储过程不知道怎么弄

[wzy_love_sly]

只能去接分的了

[JiangHongTao]

sp_helptext 存储过程名

[JiangHongTao]

select name from dbo.sysobjects where xtype='P'and status>0 order by name
获得存储过程名

函数方法一致

[JiangHongTao]

或者

select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype),    
 datalength(c.text), convert(varbinary(8000), c.text), 0 from dbo.syscomments c, dbo.sysobjects o   
  where o.id = c.id 
--and c.id = object_id(N'存储过程名') 
order by c.number, c.colid option(robust plan)

[winsor_w]

多谢，哎 好久没用这个了，连sp_helptext都忘了，这就是平时不写代码的代价啊