sql code
--½áºÏsys.indexesºÍsys.index_columns,sys.objects,sys.columns²éѯË÷ÒýËùÊôµÄ±í»òÊÓͼµÄÐÅÏ¢
select
o.name as ±íÃû,
i.name as Ë÷ÒýÃû,
c.name as ÁÐÃû,
i.type_desc as ÀàÐÍÃèÊö,
is_primary_key as Ö÷¼üÔ¼Êø,
is_unique_constraint as Î¨Ò»Ô¼Êø,
is_disabled as ½ûÓÃ
from
sys.objects o
inner join
sys.indexes i
on
i.object_id=o.object_id
inner join
sys.index_columns ic
on
ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
sys.columns c
on
ic.column_id=c.column_id and ic.object_id=c.object_id
go
--²éѯË÷ÒýµÄ¼üºÍÁÐÐÅÏ¢
select
o.name as ±íÃû,
i.name as Ë÷ÒýÃû,
c.name as ×ֶαàºÅ,
from
sysindexes i inner join sysobjects o
on
i.id=o.id
inner join
sysindexkeys k
on
o.id=k.id and i.indid=k.indid
inner join
syscolumns c
on
c.id=i.id and k.colid=c.colid
where
o.name='±íÃû'
--²éѯÊý¾Ý¿âdbÖбítbµÄËùÓÐË÷ÒýµÄËæÆ¬Çé¿ö
use db
go
select
a.index_id,---Ë÷Òý±àºÅ
b.name,---Ë÷ÒýÃû³Æ
avg_fragmentation_in_percent---Ë÷ÒýµÄÂß¼Ë鯬
from
sys.dm_db_indx_physical_stats(db_id(),object_id(N'create.consume'),null,null,null) as a
join
sys.indexes as b
on
a.object_id=b.object_id
and
a.index_id=b.index_id
go
---½âÊÍÏÂsys.dm_db_indx_physical_statsµÄ²ÎÊý
datebase_id: Êý¾Ý¿â±àºÅ£¬¿ÉÒÔʹÓÃdb_id()º¯Êý»ñȡָ¶¨Êý¾Ý¿âÃû¶ÔÓ¦µÄ±àºÅ¡£
object_id: ¸ÃË÷ÒýËùÊô±í»òÊÔͼµÄ±àºÅ
index_id: ¸ÃË÷ÒýµÄ±àºÅ
partition_number:¶ÔÏóÖзÖÇøµÄ±àºÅ
mode:ģʽÃû³Æ,ÓÃÓÚÖ¸¶¨»ñȡͳ¼ÆÐÅÏ¢µÄɨÃè¼¶±ð¡£
ÓйØsys.dm_db_indx_physical_statsµÄ½á¹û¼¯ÖеÄ×Ö¶ÎÃûÈ¥²éÏÂÁª»ú´ÔÊé¡£
---Ë÷ÒýÊÓͼ
Ë÷ÒýÊÓͼÊǾßÌ廯µÄÊÓͼ
--´´½¨Ë÷ÒýÊÓͼ
create view ÊÓͼÃû with schemabinding
as
select Óï¾ä
go
---´´½¨Ë÷ÒýÊÓͼÐèҪעÒâµÄ¼¸µã
1. ´´½¨Ë÷ÒýÊÓͼµÄʱºòÐèÒªÖ¸¶¨±íËùÊôµÄ¼Ü¹¹
--´íÎóд·¨
create view v_f with schemabinding
as
select
a.a,a.b,b.a,b.b
from
&nbs
Ïà¹ØÎĵµ£º
--8-1
USE Northwind
SELECT * from ::fn_dblog('', '')
GO
--8-2
USE Northwind
SELECT * from ::fn_dblog('', '') WHERE [Begin Time] >= '02/01/07'
GO
--9-1
SELECT *
from master.dbo.sysprocesses
--9-2
SELECT *
from sys.dm_exec_requests
--9-3
DECLARE @Handle varbinary(64);
SEL ......
MFCÖÐÓÃado·ÃÎÊSQL Server 2005Êý¾Ý¿â
½ñÌìÀÏ´ó½»´úÏîÄ¿£¬ÐèÒªMFC·ÃÎÊÁíһ̨»úÆ÷É쵀 SQL Server 2005Êý¾Ý¿â¡£MFCÎÒ²»Ê죬SQLÒ²´ÓûÓùý¡£ÔÚÍøÉϲéÁ˲»ÉÙ×ÊÁÏ£¬Ã¦ÁËÒ»ÕóÖÕÓÚ¸ãͨÁË¡£Óë¸÷λÅóÓÑ·ÖÏíһϣ¬¸ßÊÖÃǾͲ»Óÿ´ÁË£¬ÕâÊÇд¸øÏñÎÒÒ»Ñù³õѧÕߵġ£
Ò»¡¢°²×°SQL SERVER 2005£¬ÔÚ±¾»ú½¨Á¢·þÎñÆ÷ĬÈϰ²×°¼´¿É£¬Ò²¿ÉÒÔ×Ô¼ ......
50¸ö³£ÓÃsqlÓï¾ä
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ;
select a.S# from (select s#,score from SC where C#='001') a,(select s#,sc ......
ÊÓͼ²éѯÖÐÔõÑù½«Ô¶¨ÓÚÈçÐÔ±ðsex ÕâÑùµÄ×ֶΣ¬×Ö¶ÎֵΪ0£¬1ÕâÑùµÄintÀàÐÍÖµ£¬²éѯʱֱ½Ó·µ»Øvarchar
Ð͵Ä×Ö·û‘ÄÐ’£¬‘Å®’ÒÔ±ãÓÚÎÒÃǶÁÈ¡ÄØ£¿
ÓÐÈË»áÏëµ½if …else…ÕâÑùµÄÓï¾ä£¬¿ÉÊÇÔõô¼Ó£¬¶¼²»ÖªµÀ¼ÓÄÄÀÒòΪ×ÜÊÇ»á³ö´í¡£Æä ......
Á¬½Óµ½Êý¾Ý¿â·þÎñÆ÷ͨ³£Óɼ¸¸öÐèÒªºÜ³¤Ê±¼äµÄ²½Öè×é³É¡£ ±ØÐ뽨Á¢ÎïÀíͨµÀ£¨ÀýÈçÌ×½Ó×Ö»òÃüÃû¹ÜµÀ£©£¬±ØÐëÓë·þÎñÆ÷½øÐгõ´ÎÎÕÊÖ£¬±ØÐë·ÖÎöÁ¬½Ó×Ö·û´®ÐÅÏ¢£¬±ØÐëÓÉ·þÎñÆ÷¶ÔÁ¬½Ó½øÐÐÉí·ÝÑéÖ¤£¬±ØÐëÔËÐмì²éÒÔ±ãÔÚµ±Ç°ÊÂÎñÖеǼǣ¬µÈµÈ¡£
ʵ¼ÊÉÏ£¬´ó¶àÊýÓ¦ÓóÌÐò½öʹÓÃÒ»¸ö»ò¼¸¸ö²»Í¬µÄÁ¬½ÓÅäÖᣠÕâÒâζ×ÅÔÚÖ´ÐÐÓ¦ÓóÌÐòÆ ......