ASP 在线动态创建数据库的相关 SQL 命令

<%
'建立数据库连接
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};dbq="&server.mappath("data/data.mdb")
'在data.mdb中新建一个表(admin) ,字段分别为name 和password 都是文本型字段大小都是50
Dim strSQL
strSQL = "create TABLE admin (name varchar(50), password varchar(50))"
conn.Execute strSQL
%>

create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default '默认值' null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)

Drop table [表名]

Insert INTO [表名] (字段1,字段2) VALUES (100,'51WINDOWS.NET')

Delete FROM [表名] Where [字段名]>100

Update [表名] SET [字段1] = 200,[字段2] = '51WINDOWS.NET' Where [字段三]='HAIWA'

Alter TABLE [表名] ADD [字段名] NVARCHAR (50) NULL

Alter TABLE [表名] Drop COLUMN [字段名]

Alter TABLE [表名] Alter COLUMN [字段名] NVARCHAR (50) NULL

sp_rename '表名', '新表名', 'OBJECT'

Alter TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= '2000-1-1')

Alter TABLE [表名] Drop CONSTRAINT 约束名

Alter TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT '51WINDOWS.NET' FOR [字段名]

Alter TABLE [表名] Drop CONSTRAINT 默认值名

dump transaction 数据库名 with no_log
backup log 数据库名 with no_log
dbcc shrinkdatabase(数据库名)
exec sp_dboption '数据库名', 'autoshrink', 'true'

Sub AddColumn(TableName,ColumnName,ColumnType)
Conn.Execute("Alter Table "&TableName&" Add "&ColumnName&" "&ColumnType&"")
End Sub

Sub ModColumn(TableName,ColumnName,ColumnType)
Conn.Execute("Alter Table "&TableName&" Alter Column "&ColumnName&" "&ColumnType&"")
End Sub

sql="select count(*) as dida from sysobjects where id = object_id(N'[所有者].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"
set rs=conn.execute(sql)
response.write rs("dida")'返回一个数值，0代表没有，1代表存在

select * from sysobjects where id = object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1

select * from syscolumns where id = object_id(N'[dbo].[你的表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1

sql="create table mytable (m_id integer identity(1,1) primary key,m_class varchar(50) not null default 'AAA',m_int integer not null,m_numeric NUMERIC(6,2),m_money money not null default 0.00,m_memo text,m_date date default date(),m_boolean bit default yes,m_blob OLEObject,m_double double,m_float real)"
'创建表
'm_id integer identity(1,1) primary key自动编号字段并制作主键
'm_class varchar(50) not null default 'AAA'文本型，长度50，非空，默认值：AAA
'm_int integer not null数字，长整型，非空
'm_numeric NUMERIC(6,2)数字,小数，精度6，数值范围2
'm_money money not null default 0.00货币，必添字段（非空）,默认0
'm_memo text备注
'm_date date default date()日期/时间，默认当前日期（年月日）
'表中的各种数据类型基本都有了

<%
' ************ 使用ADODB.Connect对象创建 ************************
Dim oConn
Dim sDatabaseName
sDatabaseName = "CodeCreateDB"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=;"
oConn.Execute "Create DATABASE " & sDatabaseName
%>

<%@ Language=VBScript %>
<%
' ******************* 使用 SQLDMO Object 对象库 *******************
Dim oSQLServer
Dim oDatabase
Dim oDBFileData
Dim oLogFile
Dim sDatabaseName
Dim sDatapath

'数据库名字
sDatabaseName = "CodeCreateDBTest"
'数据文件保存路径
sDatapath = "D:\"

'创建Sql Server对象并进行链接,(local)处为Server名字
Set oSQLServer = Server.CreateObject("SQLDMO.SQLServer")
'    oSQLServer.LoginSecure = True             ' 使用集成验证integrated security
'    oSQLServer.Connect "(local)"
oSQLServer.Connect "(local)", "sa", "" ' 使用标准验证 standard security

' 创建Database对象
Set oDatabase =    Server.CreateObject("SQLDMO.Database")
oDatabase.Name = sDatabaseName

' 创建db文件对象
Set oDBFileData =    Server.CreateObject("SQLDMO.DBFile")
With oDBFileData
        .Name = sDatabaseName & "_data"
        .PhysicalName = sDatapath & "\" & sDatabaseName & "_data.mdf"
        .PrimaryFile = True
        .FileGrowthType = SQLDMOGrowth_MB
        .FileGrowth = 1
End With

' 创建日志文件对象
Set oLogFile =    Server.CreateObject("SQLDMO.LogFile")
With oLogFile
        .Name = sDatabaseName & "_log"
        .PhysicalName = sDatapath & "\" & sDatabaseName & "_log.ldf"
End With

'把DB文件对象和日志文件对象添加到DataBase数据库对象
oDatabase.FileGroups("PRIMARY").DBFiles.Add oDBFileData
oDatabase.TransactionLog.LogFiles.Add oLogFile

' 把数据库添加到Sql server(create the database)
oSQLServer.Databases.Add oDatabase

' 关闭连接
oSQLServer.Close

' 释放对象
Set oLogFile = Nothing
Set oDBFileData = Nothing
Set oDatabase = Nothing
Set oSQLServer = Nothing
%>