数据库系统及应用实验与课程设计指导SQL2008.doc

select*fromsys.servers
whereserver_id=0
select*fromsys.databases
select*fromsys.filegroups
select*fromsys.master_files
select*fromsys.database_files
select*fromsys.data_spaces

createtabletest
(
idintnotnull,
namechar(100)null,
constraintpk_testprimarykeyclustered(idasc)
)
createnonclusteredindexix_testontest(name)

select*
fromsys.objects
wheretype_desc='user_table'andname='test'

select*fromsys.objects
wheretype_desc='user_table'andname='test'orparent_object_idin
(selectobject_idfromsys.objects
wheretype_desc='user_table'andname='test')

select*fromsys.columns
whereobject_id='12'

select*fromsys.indexes
whereobject_id='12325345'

select*fromsys.partitions
whereobject_id='12325345'

select*fromsys.allocation_units

select*fromsys.allocation_unitsu,sys.partitionsp
whereu.typein(1,3)andu.container_id=p.hobt_idandp.object_id='33'
unionall

select*fromsys.allocation_unitsu,sys.partitionsp
whereu.type=2andu.container_id=p.partition_idandp.object_id='33'

select*fromsys.dm_db_partition_stats
whereobject_id='33'

select*fromsys.index_columns
whereobject_id='11'

select*fromsys.database_principals

select*fromsys.types

--数据库的创建
createdatabaseeduc
onprimary
(
name=student_data,
filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\student_data.mdf',
size=10MB,
maxsize=50MB,
filegrowth=1MB
)
logon
(
name=student_log,
filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\student_log.ldf',
size=2MB,
maxsize=5MB,
filegrowth=1%
)

--创建测试数据
useeduc
go
createtablestudent(svarchar(10),snamenvarchar(10),sagedatetime,ssexnvarchar(10))
insertintostudentvalues('01',N'赵雷','1990-01-01',N'男')
insertintostudentvalues('02',N'钱电','1990-12-21',N'男')
insertintostudentvalues('03',N'孙风','1990-05-20',N'男')
insertintostudentvalues('04',N'李云','1990-08-06',N'男')
insertintostudentvalues('05',N'周梅','1991-12-01',N'女')
insertintostudentvalues('06',N'吴兰','1992-03-01',N'女')
insertintostudentvalues('07',N'郑竹','1989-07-01',N'女')
insertintostudentvalues('08',N'王菊','1990-01-20',N'女')
cr