ÍøÉÏÕÒµ½µÄ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
Ïà¹ØÎĵµ£º
Ò»¡¢Ö÷ÒªÊÇÐèÒª¸üÐÂmssqlServer µÄʱºòÐèÒª½øÐÐһЩÊÇ·ñ´æÔÚµÄÅжϣ¬ÔÚmssql2000 ºÍ 2005 ÉϲâÊÔûÓÐŶÎÊÌâ
--//ÅжÏÊDZíTable_UserInfoÀïÊÇ·ñÓÐ×Ö¶Î
if not exists(select * from syscolumns where id=object_id('Table_UserInfo') and name='Tel') begin
ALTER table Table_UserInfo add Tel varchar(200)--Ôö¼Ó×Ö¶Î
......
sqlÈÕÆÚת»»²ÎÊý
--ÈÕÆÚת»»²ÎÊý,ÖµµÃÊÕ²Ø
select CONVERT(varchar, getdate(), 120)
2004-09-12 11:06:08
select convert(varchar(10),getdate() ,120)
----------
2009-04-09
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT( ......
PowerDesignerÓëSQL ServerÏàÁ¬
ÒÔǰÌý˵¹ýPowerDesigner¿ÉÒÔºÍÊý¾Ý¿âÁ¬½Ó£¬¸ù¾ÝÔÚPowerDesigner´´½¨µÄÊý¾ÝÄ£ÐÍ´´½¨±í¡¢´¥·¢Æ÷¡¢´æ´¢¹ý³Ìµ½Êý¾Ý¿âÖС£Ò²¿ÉÒÔ½«ÒÑÓеÄÊý¾Ý¿âµ¼³öµ½PowerDesignerÖÐΪÊý¾ÝÄ£ÐÍ¡£½ñÌì×öÁËһϲâÊÔ£¬·¢ÏÖȷʵºÜ¼òµ¥£¬ÏÖÔÚ²Ù×÷²½ÖèÓë´ó¼Ò·ÖÏí£º
0¡¢×¼±¸¹¤×÷
ÏÈÔÚSQL ServerÖд´½¨Ò»Êý¾Ý¿â£¬ ......
set @sql = 'select * from OPENROWSET(''SQLOLEDB'',''SERVER='+@serverip+';uid=sa;pwd=sa;Database='+@databaseName+''',''SET FMTONLY OFF;set nocount on exec procName '''''+@yqid+''''''' ) as   ......
11.3µ÷ÓųÌÐò/¼õÇá¸ºÔØ
Èç¹ûÆÚÍû¼õÇáÊý¾Ý¿â·þÎñÆ÷ÕûÌåµÄCPU»òÕßI/O¸ºÔØ£¬¿Éͨ¹ýÒÔϲ½ÖèÈ·¶¨×ÊÔ´Ãܼ¯Ð͵ÄSQLÓï¾ä£º
1.Ñ¡ÔñÔÚÓ¦ÓóÌÐòµÄ´¦Àí¸ß·åÆÚ¼ì²â¡£
2.ÔÚÒÔÉÏÖÜÆÚÄÚ£¬ÊÕ¼¯²Ù×÷ϵͳ¼°Oracleͳ¼ÆÐÅÏ¢¡£ÐèÒªÊÕ¼¯µÄOracleͳ¼ÆÐÅÏ¢ÖÁÉÙ°üÀ¨ÎļþµÄI/O£¨Í¨¹ýÊÓͼV$FILESTAT£©£¬ÏµÍ³Í³¼ÆÐÅÏ¢£¨V$SYSSTAT£©£¬ÒÔ¼°SQLͳ¼ÆÐÅÏ¢£ ......