ÍøÉÏÕÒµ½µÄSQL¿â±È½ÏÓï¾ä

ÒÔÏÂÊǵÚһƪ£º
/*--±È½ÏÁ½¸öÊý¾Ý¿âµÄ±í½á¹¹²îÒì
--*/
/*--µ÷ÓÃʾÀý
exec p_comparestructure 'xzkh_model','xzkh_new'
--*/
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
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,ĬÈÏÖµ varchar(500),×Ö¶Î˵Ã÷ varchar(500))
create table #tb2(±íÃû2 varchar(250),×Ö¶ÎÃû varchar(250),ÐòºÅ int,±êʶ bit,Ö÷¼ü bit,ÀàÐÍ varchar(250),
Õ¼ÓÃ×Ö½ÚÊý int,³¤¶È int,СÊýλÊý int,ÔÊÐí¿Õ bit,ĬÈÏÖµ varchar(500),×Ö¶Î˵Ã÷ varchar(500))
--µÃµ½Êý¾Ý¿â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 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 name in (
SELECT name