1. 建设备,用户,库
-----------------------------------------------------------------------------
-- DDL for DatabaseDevice 'mydb001'
-----------------------------------------------------------------------------
print 'mydb001'
use master
go
disk init name = 'mydb001',
physname = 'G:/ase1252/data/mydb001.dat',
size = '200M',
cntrltype = 0
go
sp_deviceattr 'mydb001', dsync, true
go
-----------------------------------------------------------------------------
-- DDL for DatabaseDevice 'mydb001_log'
-----------------------------------------------------------------------------
print 'mydb001_log'
use master
go
disk init name = 'mydb001_log',
physname = 'G:/ase1252/data/mydb001_log.dat',
size = '100M',
cntrltype = 0
go
sp_deviceattr 'mydb001_log', dsync, true
go
-----------------------------------------------------------------------------
-- DDL for Login 'na'
-----------------------------------------------------------------------------
print 'na'
exec sp_addlogin 'na', 'password', @defdb='mydb001', @deflanguage='us_english', @fullname='nax', @auth_mech = 'ASE'
exec sp_locklogin 'na', 'unlock'
go
-----------------------------------------------------------------------------
-- DDL for Database 'mydb001'
-----------------------------------------------------------------------------
print 'mydb001'
go
use master
go
create database mydb001 on mydb001 = 200
log on mydb001_log = 100
go
-----------------------------------------------------------------------------
-- Dependent DDL for Object(s)
-----------------------------------------------------------------------------
use mydb001
go
exec sp_changedbowner 'sa'
go
exec master.dbo.sp_dboption mydb001, 'select into/bulkcopy', true
go
exec master.dbo.sp_dboption mydb001, 'trunc log on chkpt', true
go
exec master.dbo.sp_dboption mydb001, 'abort tran on log full', true
go
checkpoint
go
2. 建表
-----------------------------------------------------------------------------
-- DDL for Table'mydb001.dbo.aaa'
-----------------------------------------------------------------------------
print 'Creating Table mydb001.dbo.aaa'
go
use mydb001
go
setuser 'dbo'
go
create table aaa (
name sysname(30) not null ,
id int not null ,
uid int not null ,
type char(2) not null ,
userstat smallint not null ,
sysstat smallint not null ,
indexdel smallint not null ,
schemacnt smallint not null ,
sysstat2 int not null ,
crdate datetime not null ,
expdate datetime not null ,
deltrig int not null ,
instrig int not null ,
updtrig int not null ,
seltrig int not null ,
ckfirst int not null ,
cache smallint not null ,
audflags int null ,
objspare int not null ,
versionts binary(12) null ,
loginame varchar(30) null
)
lock allpages
on 'default'
go
setuser
go
3. dbcc table
---------------------------------------------------------------
-- mydb001.dbo.aaa Check Consistency Messages from Server
---------------------------------------------------------------
--
-- dbcc checktable('mydb001.dbo.aaa')
--
Checking mydb001.dbo.aaa: Logical pagesize is 2048 bytes
The total number of data pages in this table is 2.
Table has 27 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
--
-- dbcc reindex('mydb001.dbo.aaa')
--
Checking aaa: Logical pagesize is 2048 bytes
The table 'mydb001.dbo.aaa' has no indexes.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
--
-- dbcc fix_text('mydb001.dbo.aaa')
--
There are no TEXT pages in table mydb001.dbo.aaa to be updated.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
--
-- dbcc tablealloc('mydb001.dbo.aaa', optimized, nofix)
--
***************************************************************
TABLE: mydb001.dbo.aaa OBJID = 48000171
INDID=0 FIRST=529 ROOT=530 SORT=0
Data level: 0. 2 Data pages allocated and 1 Extents allocated.
TOTAL # of extents = 1
Alloc page 512 (# of extent=1 used pages=3 ref pages=3)
Total (# of extent=1 used pages=3 ref pages=3) in this database
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.